{"cells": [{"cell_type": "markdown", "metadata": {"slideshow": {"slide_type": "slide"}}, "source": ["# Part 4 - Productivity with Pandas"]}, {"cell_type": "markdown", "metadata": {}, "source": ["<img src=\"https://upload.wikimedia.org/wikipedia/commons/thumb/e/ed/Pandas_logo.svg/512px-Pandas_logo.svg.png\">"]}, {"cell_type": "markdown", "metadata": {"toc": true}, "source": ["<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n", "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Working-with-Categorical-Data\" data-toc-modified-id=\"Working-with-Categorical-Data-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Working with Categorical Data</a></span><ul class=\"toc-item\"><li><span><a href=\"#Unique-Values-and-Value-Counts\" data-toc-modified-id=\"Unique-Values-and-Value-Counts-1.1\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Unique Values and Value Counts</a></span><ul class=\"toc-item\"><li><span><a href=\"#unique()-and-nunique()\" data-toc-modified-id=\"unique()-and-nunique()-1.1.1\"><span class=\"toc-item-num\">1.1.1&nbsp;&nbsp;</span><code>unique()</code> and <code>nunique()</code></a></span></li><li><span><a href=\"#value_counts()\" data-toc-modified-id=\"value_counts()-1.1.2\"><span class=\"toc-item-num\">1.1.2&nbsp;&nbsp;</span><code>value_counts()</code></a></span></li></ul></li><li><span><a href=\"#One-Hot-Encoding\" data-toc-modified-id=\"One-Hot-Encoding-1.2\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>One Hot Encoding</a></span></li><li><span><a href=\"#Binning-Continous-Variables\" data-toc-modified-id=\"Binning-Continous-Variables-1.3\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Binning Continous Variables</a></span></li></ul></li><li><span><a href=\"#Data-Aggregation\" data-toc-modified-id=\"Data-Aggregation-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Data Aggregation</a></span><ul class=\"toc-item\"><li><span><a href=\"#GroupBy\" data-toc-modified-id=\"GroupBy-2.1\"><span class=\"toc-item-num\">2.1&nbsp;&nbsp;</span>GroupBy</a></span><ul class=\"toc-item\"><li><span><a href=\"#Using-Multiple-Keys\" data-toc-modified-id=\"Using-Multiple-Keys-2.1.1\"><span class=\"toc-item-num\">2.1.1&nbsp;&nbsp;</span>Using Multiple Keys</a></span></li><li><span><a href=\"#Using-aggregate()\" data-toc-modified-id=\"Using-aggregate()-2.1.2\"><span class=\"toc-item-num\">2.1.2&nbsp;&nbsp;</span>Using <code>aggregate()</code></a></span></li><li><span><a href=\"#Selecting-a-Subset-of-Columns\" data-toc-modified-id=\"Selecting-a-Subset-of-Columns-2.1.3\"><span class=\"toc-item-num\">2.1.3&nbsp;&nbsp;</span>Selecting a Subset of Columns</a></span></li></ul></li><li><span><a href=\"#Pivot-Tables\" data-toc-modified-id=\"Pivot-Tables-2.2\"><span class=\"toc-item-num\">2.2&nbsp;&nbsp;</span>Pivot Tables</a></span><ul class=\"toc-item\"><li><span><a href=\"#Multi-level-Pivot-Table\" data-toc-modified-id=\"Multi-level-Pivot-Table-2.2.1\"><span class=\"toc-item-num\">2.2.1&nbsp;&nbsp;</span>Multi-level Pivot Table</a></span></li><li><span><a href=\"#Using-aggfunc\" data-toc-modified-id=\"Using-aggfunc-2.2.2\"><span class=\"toc-item-num\">2.2.2&nbsp;&nbsp;</span>Using <code>aggfunc</code></a></span></li></ul></li><li><span><a href=\"#describe()-method\" data-toc-modified-id=\"describe()-method-2.3\"><span class=\"toc-item-num\">2.3&nbsp;&nbsp;</span><code>describe()</code> method</a></span><ul class=\"toc-item\"><li><span><a href=\"#describe()-on-GroupBy\" data-toc-modified-id=\"describe()-on-GroupBy-2.3.1\"><span class=\"toc-item-num\">2.3.1&nbsp;&nbsp;</span><code>describe()</code> on GroupBy</a></span></li></ul></li></ul></li><li><span><a href=\"#Combining-Data\" data-toc-modified-id=\"Combining-Data-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Combining Data</a></span><ul class=\"toc-item\"><li><span><a href=\"#concat()\" data-toc-modified-id=\"concat()-3.1\"><span class=\"toc-item-num\">3.1&nbsp;&nbsp;</span><code>concat()</code></a></span></li><li><span><a href=\"#append()\" data-toc-modified-id=\"append()-3.2\"><span class=\"toc-item-num\">3.2&nbsp;&nbsp;</span><code>append()</code></a></span></li><li><span><a href=\"#merge()\" data-toc-modified-id=\"merge()-3.3\"><span class=\"toc-item-num\">3.3&nbsp;&nbsp;</span><code>merge()</code></a></span><ul class=\"toc-item\"><li><span><a href=\"#Inner-Join\" data-toc-modified-id=\"Inner-Join-3.3.1\"><span class=\"toc-item-num\">3.3.1&nbsp;&nbsp;</span>Inner Join</a></span></li><li><span><a href=\"#Left-Join\" data-toc-modified-id=\"Left-Join-3.3.2\"><span class=\"toc-item-num\">3.3.2&nbsp;&nbsp;</span>Left Join</a></span></li><li><span><a href=\"#Right-Join\" data-toc-modified-id=\"Right-Join-3.3.3\"><span class=\"toc-item-num\">3.3.3&nbsp;&nbsp;</span>Right Join</a></span></li><li><span><a href=\"#Outer-Join\" data-toc-modified-id=\"Outer-Join-3.3.4\"><span class=\"toc-item-num\">3.3.4&nbsp;&nbsp;</span>Outer Join</a></span></li><li><span><a href=\"#Using-index-to-merge\" data-toc-modified-id=\"Using-index-to-merge-3.3.5\"><span class=\"toc-item-num\">3.3.5&nbsp;&nbsp;</span>Using <strong>index</strong> to merge</a></span></li></ul></li><li><span><a href=\"#join()\" data-toc-modified-id=\"join()-3.4\"><span class=\"toc-item-num\">3.4&nbsp;&nbsp;</span><code>join()</code></a></span></li></ul></li><li><span><a href=\"#Hierarchical-Indexing\" data-toc-modified-id=\"Hierarchical-Indexing-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Hierarchical Indexing</a></span><ul class=\"toc-item\"><li><span><a href=\"#Multi-indexed-Series\" data-toc-modified-id=\"Multi-indexed-Series-4.1\"><span class=\"toc-item-num\">4.1&nbsp;&nbsp;</span>Multi-indexed Series</a></span><ul class=\"toc-item\"><li><span><a href=\"#Subset-Selection\" data-toc-modified-id=\"Subset-Selection-4.1.1\"><span class=\"toc-item-num\">4.1.1&nbsp;&nbsp;</span>Subset Selection</a></span></li><li><span><a href=\"#unstack()-and-stack()\" data-toc-modified-id=\"unstack()-and-stack()-4.1.2\"><span class=\"toc-item-num\">4.1.2&nbsp;&nbsp;</span><code>unstack()</code> and <code>stack()</code></a></span></li></ul></li><li><span><a href=\"#Multi-indexed-DataFrame\" data-toc-modified-id=\"Multi-indexed-DataFrame-4.2\"><span class=\"toc-item-num\">4.2&nbsp;&nbsp;</span>Multi-indexed DataFrame</a></span><ul class=\"toc-item\"><li><span><a href=\"#Subset-Selection\" data-toc-modified-id=\"Subset-Selection-4.2.1\"><span class=\"toc-item-num\">4.2.1&nbsp;&nbsp;</span>Subset Selection</a></span></li><li><span><a href=\"#Sorting\" data-toc-modified-id=\"Sorting-4.2.2\"><span class=\"toc-item-num\">4.2.2&nbsp;&nbsp;</span>Sorting</a></span><ul class=\"toc-item\"><li><span><a href=\"#By-Index-and-Level\" data-toc-modified-id=\"By-Index-and-Level-4.2.2.1\"><span class=\"toc-item-num\">4.2.2.1&nbsp;&nbsp;</span>By Index and Level</a></span></li><li><span><a href=\"#By-Value\" data-toc-modified-id=\"By-Value-4.2.2.2\"><span class=\"toc-item-num\">4.2.2.2&nbsp;&nbsp;</span>By Value</a></span></li></ul></li><li><span><a href=\"#Data-Aggregations\" data-toc-modified-id=\"Data-Aggregations-4.2.3\"><span class=\"toc-item-num\">4.2.3&nbsp;&nbsp;</span>Data Aggregations</a></span></li></ul></li></ul></li><li><span><a href=\"#Conclusion\" data-toc-modified-id=\"Conclusion-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Conclusion</a></span></li><li><span><a href=\"#References\" data-toc-modified-id=\"References-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>References</a></span></li></ul></div>"]}, {"attachments": {}, "cell_type": "markdown", "metadata": {}, "source": ["In the previous notebook, we introduced __[Pandas](https://pandas.pydata.org/)__, which provides high-level data structures and functions designed to make working with structured or tabular data fast, easy, and expressive.\n", "\n", "In this notebook, we will build on our knowledge of Pandas to be more productive. Pandas provides sophisticated, multi-level indexing functionality, along with the ability to perform data aggregation operations, such as grouping, merging, and joining data. It also provides capabilities for working with Time Series data that involves navigating and manipulating various date ranges and time indices. Let's dive into the details."]}, {"cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": ["# Import libraries\n", "import pandas as pd\n", "import numpy as np"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Working with Categorical Data"]}, {"attachments": {}, "cell_type": "markdown", "metadata": {}, "source": ["In many practical Data Science activities, you may come across data that contain categorical variables. These variables are typically stored as text values in columns. For such data, you may want to find the unique elements, frequency of each category present, or transform the categorical data into suitable numeric values.\n", "\n", "__Pandas__ provides various approaches to handle categorical data. To get started, let's create a small dataset and look at some examples. __Seaborn__ library comes preloaded with some sample datasets. We will load the `tips` data from `seaborn` for our analysis."]}, {"cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["   total_bill   tip     sex smoker  day    time  size\n", "0       16.99  1.01  Female     No  Sun  Dinner     2\n", "1       10.34  1.66    Male     No  Sun  Dinner     3\n", "2       21.01  3.50    Male     No  Sun  Dinner     3\n", "3       23.68  3.31    Male     No  Sun  Dinner     2\n", "4       24.59  3.61  Female     No  Sun  Dinner     4\n"]}], "source": ["import seaborn as sns\n", "tips_data = sns.load_dataset('tips')\n", "print(tips_data.head())"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### Unique Values and Value Counts\n", " __Pandas__ provides methods such as `unique()`, `nunique()`, and `value_counts()` to extract information about the values in a column."]}, {"cell_type": "markdown", "metadata": {}, "source": ["####  `unique()` and `nunique()`\n", "`unique()` can be used to identify the unique elements of a column."]}, {"cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [{"data": {"text/plain": ["[Sun, Sat, Thur, Fri]\n", "Categories (4, object): [Sun, Sat, Thur, Fri]"]}, "execution_count": 3, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data['day'].unique()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The result is an `array` which can be easily converted to a list by chaining the `tolist()` function."]}, {"cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [{"data": {"text/plain": ["['Sun', 'Sat', 'Thur', 'Fri']"]}, "execution_count": 4, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data['day'].unique().tolist()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Similarly, `unique()` can be applied on the `index`."]}, {"cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "      <th>sex</th>\n", "      <th>smoker</th>\n", "      <th>time</th>\n", "      <th>size</th>\n", "    </tr>\n", "    <tr>\n", "      <th>day</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>16.99</td>\n", "      <td>1.01</td>\n", "      <td>Female</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>10.34</td>\n", "      <td>1.66</td>\n", "      <td>Male</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>21.01</td>\n", "      <td>3.50</td>\n", "      <td>Male</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>23.68</td>\n", "      <td>3.31</td>\n", "      <td>Male</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>24.59</td>\n", "      <td>3.61</td>\n", "      <td>Female</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>4</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["     total_bill   tip     sex smoker    time  size\n", "day                                               \n", "Sun       16.99  1.01  Female     No  Dinner     2\n", "Sun       10.34  1.66    Male     No  Dinner     3\n", "Sun       21.01  3.50    Male     No  Dinner     3\n", "Sun       23.68  3.31    Male     No  Dinner     2\n", "Sun       24.59  3.61  Female     No  Dinner     4"]}, "execution_count": 5, "metadata": {}, "output_type": "execute_result"}], "source": ["indexed_tip = tips_data.set_index('day')\n", "indexed_tip.head()"]}, {"cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [{"data": {"text/plain": ["CategoricalIndex(['Sun', 'Sat', 'Thur', 'Fri'], categories=['Sun', 'Sat', 'Thur', 'Fri'], ordered=False, name='day', dtype='category')"]}, "execution_count": 6, "metadata": {}, "output_type": "execute_result"}], "source": ["indexed_tip.index.unique()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["__`nunique()`__ can be used to count the number of unique values in a column."]}, {"cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [{"data": {"text/plain": ["4"]}, "execution_count": 7, "metadata": {}, "output_type": "execute_result"}], "source": ["# Count of unique values in employee\n", "tips_data['day'].nunique()"]}, {"cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [{"data": {"text/plain": ["2"]}, "execution_count": 8, "metadata": {}, "output_type": "execute_result"}], "source": ["# Count of unique values in skills\n", "tips_data['time'].nunique()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### `value_counts()`\n", "`value_counts()` are used to determine the frequency of different values present in the column."]}, {"cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [{"data": {"text/plain": ["Sat     87\n", "Sun     76\n", "Thur    62\n", "Fri     19\n", "Name: day, dtype: int64"]}, "execution_count": 9, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data['day'].value_counts()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["`reset_index()` can be chained to the `value_counts()` operation to easily get the results as a `DataFrame`."]}, {"cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>index</th>\n", "      <th>day</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>Sat</td>\n", "      <td>87</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>Sun</td>\n", "      <td>76</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Thur</td>\n", "      <td>62</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Fri</td>\n", "      <td>19</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  index  day\n", "0   Sat   87\n", "1   Sun   76\n", "2  Thur   62\n", "3   Fri   19"]}, "execution_count": 10, "metadata": {}, "output_type": "execute_result"}], "source": ["days_df = tips_data['day'].value_counts().reset_index()\n", "days_df"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### One Hot Encoding\n", "Many machine learning algorithms do not support the presence of categorical values in data. Pandas provides various approaches to transform the categorical data into suitable numeric values to create _dummy variables_, and one such approach is called __One Hot Encoding__. The basic strategy is to convert each category value into a new column and assign a `0 or 1` (True/False) value to the column. Dummy variables can be created using `get_dummies`."]}, {"cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "      <th>sex</th>\n", "      <th>smoker</th>\n", "      <th>time</th>\n", "      <th>size</th>\n", "      <th>day_Thur</th>\n", "      <th>day_Fri</th>\n", "      <th>day_Sat</th>\n", "      <th>day_Sun</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>16.99</td>\n", "      <td>1.01</td>\n", "      <td>Female</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>10.34</td>\n", "      <td>1.66</td>\n", "      <td>Male</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>21.01</td>\n", "      <td>3.50</td>\n", "      <td>Male</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>23.68</td>\n", "      <td>3.31</td>\n", "      <td>Male</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>24.59</td>\n", "      <td>3.61</td>\n", "      <td>Female</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>4</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>...</th>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "    </tr>\n", "    <tr>\n", "      <th>239</th>\n", "      <td>29.03</td>\n", "      <td>5.92</td>\n", "      <td>Male</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>240</th>\n", "      <td>27.18</td>\n", "      <td>2.00</td>\n", "      <td>Female</td>\n", "      <td>Yes</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>241</th>\n", "      <td>22.67</td>\n", "      <td>2.00</td>\n", "      <td>Male</td>\n", "      <td>Yes</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>242</th>\n", "      <td>17.82</td>\n", "      <td>1.75</td>\n", "      <td>Male</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>243</th>\n", "      <td>18.78</td>\n", "      <td>3.00</td>\n", "      <td>Female</td>\n", "      <td>No</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "<p>244 rows \u00d7 10 columns</p>\n", "</div>"], "text/plain": ["     total_bill   tip     sex smoker    time  size  day_Thur  day_Fri  \\\n", "0         16.99  1.01  Female     No  Dinner     2         0        0   \n", "1         10.34  1.66    Male     No  Dinner     3         0        0   \n", "2         21.01  3.50    Male     No  Dinner     3         0        0   \n", "3         23.68  3.31    Male     No  Dinner     2         0        0   \n", "4         24.59  3.61  Female     No  Dinner     4         0        0   \n", "..          ...   ...     ...    ...     ...   ...       ...      ...   \n", "239       29.03  5.92    Male     No  Dinner     3         0        0   \n", "240       27.18  2.00  Female    Yes  Dinner     2         0        0   \n", "241       22.67  2.00    Male    Yes  Dinner     2         0        0   \n", "242       17.82  1.75    Male     No  Dinner     2         0        0   \n", "243       18.78  3.00  Female     No  Dinner     2         1        0   \n", "\n", "     day_Sat  day_Sun  \n", "0          0        1  \n", "1          0        1  \n", "2          0        1  \n", "3          0        1  \n", "4          0        1  \n", "..       ...      ...  \n", "239        1        0  \n", "240        1        0  \n", "241        1        0  \n", "242        1        0  \n", "243        0        0  \n", "\n", "[244 rows x 10 columns]"]}, "execution_count": 11, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.get_dummies(tips_data, columns=['day'])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The resulting dataset contains four new columns (one for each day) `day_Thur, day_Fri, day_Sat, day_Sun`. You can pass as many category columns as you would like and choose how to label the columns using `prefix` parameter."]}, {"cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "      <th>time</th>\n", "      <th>size</th>\n", "      <th>weekday_Thur</th>\n", "      <th>weekday_Fri</th>\n", "      <th>weekday_Sat</th>\n", "      <th>weekday_Sun</th>\n", "      <th>smokes_Yes</th>\n", "      <th>smokes_No</th>\n", "      <th>gender_Male</th>\n", "      <th>gender_Female</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>16.99</td>\n", "      <td>1.01</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>10.34</td>\n", "      <td>1.66</td>\n", "      <td>Dinner</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>21.01</td>\n", "      <td>3.50</td>\n", "      <td>Dinner</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>23.68</td>\n", "      <td>3.31</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>24.59</td>\n", "      <td>3.61</td>\n", "      <td>Dinner</td>\n", "      <td>4</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>...</th>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "      <td>...</td>\n", "    </tr>\n", "    <tr>\n", "      <th>239</th>\n", "      <td>29.03</td>\n", "      <td>5.92</td>\n", "      <td>Dinner</td>\n", "      <td>3</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>240</th>\n", "      <td>27.18</td>\n", "      <td>2.00</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>241</th>\n", "      <td>22.67</td>\n", "      <td>2.00</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>242</th>\n", "      <td>17.82</td>\n", "      <td>1.75</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>243</th>\n", "      <td>18.78</td>\n", "      <td>3.00</td>\n", "      <td>Dinner</td>\n", "      <td>2</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "<p>244 rows \u00d7 12 columns</p>\n", "</div>"], "text/plain": ["     total_bill   tip    time  size  weekday_Thur  weekday_Fri  weekday_Sat  \\\n", "0         16.99  1.01  Dinner     2             0            0            0   \n", "1         10.34  1.66  Dinner     3             0            0            0   \n", "2         21.01  3.50  Dinner     3             0            0            0   \n", "3         23.68  3.31  Dinner     2             0            0            0   \n", "4         24.59  3.61  Dinner     4             0            0            0   \n", "..          ...   ...     ...   ...           ...          ...          ...   \n", "239       29.03  5.92  Dinner     3             0            0            1   \n", "240       27.18  2.00  Dinner     2             0            0            1   \n", "241       22.67  2.00  Dinner     2             0            0            1   \n", "242       17.82  1.75  Dinner     2             0            0            1   \n", "243       18.78  3.00  Dinner     2             1            0            0   \n", "\n", "     weekday_Sun  smokes_Yes  smokes_No  gender_Male  gender_Female  \n", "0              1           0          1            0              1  \n", "1              1           0          1            1              0  \n", "2              1           0          1            1              0  \n", "3              1           0          1            1              0  \n", "4              1           0          1            0              1  \n", "..           ...         ...        ...          ...            ...  \n", "239            0           0          1            1              0  \n", "240            0           1          0            0              1  \n", "241            0           1          0            1              0  \n", "242            0           0          1            1              0  \n", "243            0           0          1            0              1  \n", "\n", "[244 rows x 12 columns]"]}, "execution_count": 12, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.get_dummies(tips_data, columns=['day', 'smoker', 'sex'], prefix=['weekday', 'smokes', 'gender'])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### Binning Continous Variables\n", "You may come across scenarios where you need to bin continuous data into discrete chunks to be used as a categorical variable. We can use __`pd.cut()`__ function to cut our data into discrete buckets."]}, {"cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [{"data": {"text/plain": ["0      (12.618, 22.166]\n", "1       (3.022, 12.618]\n", "2      (12.618, 22.166]\n", "3      (22.166, 31.714]\n", "4      (22.166, 31.714]\n", "             ...       \n", "239    (22.166, 31.714]\n", "240    (22.166, 31.714]\n", "241    (22.166, 31.714]\n", "242    (12.618, 22.166]\n", "243    (12.618, 22.166]\n", "Name: total_bill, Length: 244, dtype: category\n", "Categories (5, interval[float64]): [(3.022, 12.618] < (12.618, 22.166] < (22.166, 31.714] < (31.714, 41.262] < (41.262, 50.81]]"]}, "execution_count": 13, "metadata": {}, "output_type": "execute_result"}], "source": ["# Bin data into 5 equal sized buckets\n", "pd.cut(tips_data['total_bill'], bins=5)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The function results in five equal-width bins. We can also specify bin edges to create specific _non-uniform_ bins."]}, {"cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [{"data": {"text/plain": ["0      (0.0, 31.714]\n", "1      (0.0, 31.714]\n", "2      (0.0, 31.714]\n", "3      (0.0, 31.714]\n", "4      (0.0, 31.714]\n", "           ...      \n", "239    (0.0, 31.714]\n", "240    (0.0, 31.714]\n", "241    (0.0, 31.714]\n", "242    (0.0, 31.714]\n", "243    (0.0, 31.714]\n", "Name: total_bill, Length: 244, dtype: category\n", "Categories (2, interval[float64]): [(0.0, 31.714] < (31.714, 50.81]]"]}, "execution_count": 14, "metadata": {}, "output_type": "execute_result"}], "source": ["# Bin data by specifying bin edges\n", "bill_cat = pd.cut(tips_data['total_bill'], bins=[0, 31.714, 50.81])\n", "bill_cat"]}, {"cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [{"data": {"text/plain": ["(0.0, 31.714]      218\n", "(31.714, 50.81]     26\n", "Name: total_bill, dtype: int64"]}, "execution_count": 15, "metadata": {}, "output_type": "execute_result"}], "source": ["# Value count for each category\n", "bill_cat.value_counts()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The operation creates two non-uniform categories for `total_bill`."]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Data Aggregation"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Summarizing data by applying various aggregation functions such as `sum()`, `mean()`, `median()` etc. to each group or category within the data is a critical component of a data analysis workflow. Simple aggregations can give you a high level overview but are often not enough to get a deeper understanding of the data.  \n", "\n", "__Pandas__ provides a flexible `groupby()` operation which allows for quick and efficient aggregation on subsets of data."]}, {"cell_type": "markdown", "metadata": {}, "source": ["### GroupBy"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The name \"group by\" comes from a command in the [SQL](https://en.wikipedia.org/wiki/SQL) language. Hadley Wickham, author of popular packages in [R](https://www.r-project.org/about.html) programming language, described grouping operations by coining the term _split-apply-combine_.\n", "\n", "- The _split_ step breaks up and groups a DataFrame based on the value of the specified key. Splitting is performed on a particular axis of a DataFrame i.e. rows (axis=0) or columns (axis=1).\n", "- The _apply_ step computes some aggregation function within the individual groups.\n", "- The _combine_ step merges the results of these operations into an output array."]}, {"cell_type": "markdown", "metadata": {}, "source": ["The image below shows a mockup of a simple group aggregation.\n", "<img src=\"\">"]}, {"cell_type": "markdown", "metadata": {}, "source": ["`groupby()` method can be used to apply the basic _split-apply-combine_ operation on a DataFrame by specifying the desired column name."]}, {"cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [{"data": {"text/plain": ["<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7ff1186b91c0>"]}, "execution_count": 16, "metadata": {}, "output_type": "execute_result"}], "source": ["# Apply Group By\n", "grp1 = tips_data.groupby('sex')\n", "grp1"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The method returns a `DataFrameGroupBy` object. No actual computation has been performed by the `groupby()` method yet. The idea is that this object has all the information needed to then apply some operation to each of the groups in the data. This \"lazy evaluation\" approach means that common aggregation functions can be implemented very efficiently using `groupby()`. For example, to compute the mean, we can call `mean()` method on the _GroupBy_ object."]}, {"cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "      <th>size</th>\n", "    </tr>\n", "    <tr>\n", "      <th>sex</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>Male</th>\n", "      <td>20.744076</td>\n", "      <td>3.089618</td>\n", "      <td>2.630573</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Female</th>\n", "      <td>18.056897</td>\n", "      <td>2.833448</td>\n", "      <td>2.459770</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["        total_bill       tip      size\n", "sex                                   \n", "Male     20.744076  3.089618  2.630573\n", "Female   18.056897  2.833448  2.459770"]}, "execution_count": 17, "metadata": {}, "output_type": "execute_result"}], "source": ["# Compute mean\n", "grp1.mean()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The data has been aggregated according to the group key and is now indexed by the unique values in the `sex` column. By default, all of the numeric columns are aggregated. "]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Using Multiple Keys\n", "Multiple column names can be passed as group keys to group the data appropriately. Let's group the data by `smoker` and `day` columns."]}, {"cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "      <th>size</th>\n", "    </tr>\n", "    <tr>\n", "      <th>smoker</th>\n", "      <th>day</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">Yes</th>\n", "      <th>Thur</th>\n", "      <td>19.190588</td>\n", "      <td>3.030000</td>\n", "      <td>2.352941</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>16.813333</td>\n", "      <td>2.714000</td>\n", "      <td>2.066667</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>21.276667</td>\n", "      <td>2.875476</td>\n", "      <td>2.476190</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>24.120000</td>\n", "      <td>3.516842</td>\n", "      <td>2.578947</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">No</th>\n", "      <th>Thur</th>\n", "      <td>17.113111</td>\n", "      <td>2.673778</td>\n", "      <td>2.488889</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>18.420000</td>\n", "      <td>2.812500</td>\n", "      <td>2.250000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>19.661778</td>\n", "      <td>3.102889</td>\n", "      <td>2.555556</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>20.506667</td>\n", "      <td>3.167895</td>\n", "      <td>2.929825</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["             total_bill       tip      size\n", "smoker day                                 \n", "Yes    Thur   19.190588  3.030000  2.352941\n", "       Fri    16.813333  2.714000  2.066667\n", "       Sat    21.276667  2.875476  2.476190\n", "       Sun    24.120000  3.516842  2.578947\n", "No     Thur   17.113111  2.673778  2.488889\n", "       Fri    18.420000  2.812500  2.250000\n", "       Sat    19.661778  3.102889  2.555556\n", "       Sun    20.506667  3.167895  2.929825"]}, "execution_count": 18, "metadata": {}, "output_type": "execute_result"}], "source": ["# Aggregation using multiple keys\n", "tips_data.groupby(['smoker', 'day']).mean()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The data is now indexed by the unique values in the `smoker` and `day` columns. Similarly, other aggregation operations such as `sum()`, `median()`, `std()` etc. can be applied to the groups within data. "]}, {"cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "      <th>size</th>\n", "    </tr>\n", "    <tr>\n", "      <th>smoker</th>\n", "      <th>day</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">Yes</th>\n", "      <th>Thur</th>\n", "      <td>326.24</td>\n", "      <td>51.51</td>\n", "      <td>40</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>252.20</td>\n", "      <td>40.71</td>\n", "      <td>31</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>893.62</td>\n", "      <td>120.77</td>\n", "      <td>104</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>458.28</td>\n", "      <td>66.82</td>\n", "      <td>49</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">No</th>\n", "      <th>Thur</th>\n", "      <td>770.09</td>\n", "      <td>120.32</td>\n", "      <td>112</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>73.68</td>\n", "      <td>11.25</td>\n", "      <td>9</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>884.78</td>\n", "      <td>139.63</td>\n", "      <td>115</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>1168.88</td>\n", "      <td>180.57</td>\n", "      <td>167</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["             total_bill     tip  size\n", "smoker day                           \n", "Yes    Thur      326.24   51.51    40\n", "       Fri       252.20   40.71    31\n", "       Sat       893.62  120.77   104\n", "       Sun       458.28   66.82    49\n", "No     Thur      770.09  120.32   112\n", "       Fri        73.68   11.25     9\n", "       Sat       884.78  139.63   115\n", "       Sun      1168.88  180.57   167"]}, "execution_count": 19, "metadata": {}, "output_type": "execute_result"}], "source": ["# Sum operation\n", "tips_data.groupby(['smoker', 'day']).sum()"]}, {"cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "      <th>size</th>\n", "    </tr>\n", "    <tr>\n", "      <th>smoker</th>\n", "      <th>day</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">Yes</th>\n", "      <th>Thur</th>\n", "      <td>16.470</td>\n", "      <td>2.560</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>13.420</td>\n", "      <td>2.500</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>20.390</td>\n", "      <td>2.690</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>23.100</td>\n", "      <td>3.500</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">No</th>\n", "      <th>Thur</th>\n", "      <td>15.950</td>\n", "      <td>2.180</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>19.235</td>\n", "      <td>3.125</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>17.820</td>\n", "      <td>2.750</td>\n", "      <td>2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>18.430</td>\n", "      <td>3.020</td>\n", "      <td>3</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["             total_bill    tip  size\n", "smoker day                          \n", "Yes    Thur      16.470  2.560     2\n", "       Fri       13.420  2.500     2\n", "       Sat       20.390  2.690     2\n", "       Sun       23.100  3.500     2\n", "No     Thur      15.950  2.180     2\n", "       Fri       19.235  3.125     2\n", "       Sat       17.820  2.750     2\n", "       Sun       18.430  3.020     3"]}, "execution_count": 20, "metadata": {}, "output_type": "execute_result"}], "source": ["# Median operation\n", "tips_data.groupby(['smoker', 'day']).median()"]}, {"cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "      <th>size</th>\n", "    </tr>\n", "    <tr>\n", "      <th>smoker</th>\n", "      <th>day</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">Yes</th>\n", "      <th>Thur</th>\n", "      <td>8.355149</td>\n", "      <td>1.113491</td>\n", "      <td>0.701888</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>9.086388</td>\n", "      <td>1.077668</td>\n", "      <td>0.593617</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>10.069138</td>\n", "      <td>1.630580</td>\n", "      <td>0.862161</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>10.442511</td>\n", "      <td>1.261151</td>\n", "      <td>0.901591</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">No</th>\n", "      <th>Thur</th>\n", "      <td>7.721728</td>\n", "      <td>1.282964</td>\n", "      <td>1.179796</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>5.059282</td>\n", "      <td>0.898494</td>\n", "      <td>0.500000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>8.939181</td>\n", "      <td>1.642088</td>\n", "      <td>0.784960</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>8.130189</td>\n", "      <td>1.224785</td>\n", "      <td>1.032674</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["             total_bill       tip      size\n", "smoker day                                 \n", "Yes    Thur    8.355149  1.113491  0.701888\n", "       Fri     9.086388  1.077668  0.593617\n", "       Sat    10.069138  1.630580  0.862161\n", "       Sun    10.442511  1.261151  0.901591\n", "No     Thur    7.721728  1.282964  1.179796\n", "       Fri     5.059282  0.898494  0.500000\n", "       Sat     8.939181  1.642088  0.784960\n", "       Sun     8.130189  1.224785  1.032674"]}, "execution_count": 21, "metadata": {}, "output_type": "execute_result"}], "source": ["# Standard Deviation operation\n", "tips_data.groupby(['smoker', 'day']).std()"]}, {"attachments": {}, "cell_type": "markdown", "metadata": {}, "source": ["#### Using `aggregate()`\n", "`aggregate()` method allows for even greater flexibility by taking a string, a function, or a list and computing all the aggregates at once. The example below shows minimum aggregation operation being used as a string, median being called as a function, and all aggregation operations being passed as a list."]}, {"cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th></th>\n", "      <th colspan=\"3\" halign=\"left\">total_bill</th>\n", "      <th colspan=\"3\" halign=\"left\">tip</th>\n", "      <th colspan=\"3\" halign=\"left\">size</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th></th>\n", "      <th>min</th>\n", "      <th>median</th>\n", "      <th>max</th>\n", "      <th>min</th>\n", "      <th>median</th>\n", "      <th>max</th>\n", "      <th>min</th>\n", "      <th>median</th>\n", "      <th>max</th>\n", "    </tr>\n", "    <tr>\n", "      <th>smoker</th>\n", "      <th>day</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">Yes</th>\n", "      <th>Thur</th>\n", "      <td>10.34</td>\n", "      <td>16.470</td>\n", "      <td>43.11</td>\n", "      <td>2.00</td>\n", "      <td>2.560</td>\n", "      <td>5.00</td>\n", "      <td>2</td>\n", "      <td>2</td>\n", "      <td>4</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>5.75</td>\n", "      <td>13.420</td>\n", "      <td>40.17</td>\n", "      <td>1.00</td>\n", "      <td>2.500</td>\n", "      <td>4.73</td>\n", "      <td>1</td>\n", "      <td>2</td>\n", "      <td>4</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>3.07</td>\n", "      <td>20.390</td>\n", "      <td>50.81</td>\n", "      <td>1.00</td>\n", "      <td>2.690</td>\n", "      <td>10.00</td>\n", "      <td>1</td>\n", "      <td>2</td>\n", "      <td>5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>7.25</td>\n", "      <td>23.100</td>\n", "      <td>45.35</td>\n", "      <td>1.50</td>\n", "      <td>3.500</td>\n", "      <td>6.50</td>\n", "      <td>2</td>\n", "      <td>2</td>\n", "      <td>5</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">No</th>\n", "      <th>Thur</th>\n", "      <td>7.51</td>\n", "      <td>15.950</td>\n", "      <td>41.19</td>\n", "      <td>1.25</td>\n", "      <td>2.180</td>\n", "      <td>6.70</td>\n", "      <td>1</td>\n", "      <td>2</td>\n", "      <td>6</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>12.46</td>\n", "      <td>19.235</td>\n", "      <td>22.75</td>\n", "      <td>1.50</td>\n", "      <td>3.125</td>\n", "      <td>3.50</td>\n", "      <td>2</td>\n", "      <td>2</td>\n", "      <td>3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>7.25</td>\n", "      <td>17.820</td>\n", "      <td>48.33</td>\n", "      <td>1.00</td>\n", "      <td>2.750</td>\n", "      <td>9.00</td>\n", "      <td>1</td>\n", "      <td>2</td>\n", "      <td>4</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>8.77</td>\n", "      <td>18.430</td>\n", "      <td>48.17</td>\n", "      <td>1.01</td>\n", "      <td>3.020</td>\n", "      <td>6.00</td>\n", "      <td>2</td>\n", "      <td>3</td>\n", "      <td>6</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["            total_bill                  tip               size           \n", "                   min  median    max   min median    max  min median max\n", "smoker day                                                               \n", "Yes    Thur      10.34  16.470  43.11  2.00  2.560   5.00    2      2   4\n", "       Fri        5.75  13.420  40.17  1.00  2.500   4.73    1      2   4\n", "       Sat        3.07  20.390  50.81  1.00  2.690  10.00    1      2   5\n", "       Sun        7.25  23.100  45.35  1.50  3.500   6.50    2      2   5\n", "No     Thur       7.51  15.950  41.19  1.25  2.180   6.70    1      2   6\n", "       Fri       12.46  19.235  22.75  1.50  3.125   3.50    2      2   3\n", "       Sat        7.25  17.820  48.33  1.00  2.750   9.00    1      2   4\n", "       Sun        8.77  18.430  48.17  1.01  3.020   6.00    2      3   6"]}, "execution_count": 22, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data.groupby(['smoker', 'day']).aggregate(['min', np.median, max])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Aggregation functions can also be passed as a dictionary, mapping column names to operations that are to be applied on that column. The example below shows `min` operation applied to `total_bill` column and `max` operation applied to `tip` column."]}, {"cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "    </tr>\n", "    <tr>\n", "      <th>smoker</th>\n", "      <th>day</th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">Yes</th>\n", "      <th>Thur</th>\n", "      <td>10.34</td>\n", "      <td>5.00</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>5.75</td>\n", "      <td>4.73</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>3.07</td>\n", "      <td>10.00</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>7.25</td>\n", "      <td>6.50</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">No</th>\n", "      <th>Thur</th>\n", "      <td>7.51</td>\n", "      <td>6.70</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>12.46</td>\n", "      <td>3.50</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>7.25</td>\n", "      <td>9.00</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>8.77</td>\n", "      <td>6.00</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["             total_bill    tip\n", "smoker day                    \n", "Yes    Thur       10.34   5.00\n", "       Fri         5.75   4.73\n", "       Sat         3.07  10.00\n", "       Sun         7.25   6.50\n", "No     Thur        7.51   6.70\n", "       Fri        12.46   3.50\n", "       Sat         7.25   9.00\n", "       Sun         8.77   6.00"]}, "execution_count": 23, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data.groupby(['smoker', 'day']).aggregate({'total_bill':'min', 'tip':'max'})"]}, {"cell_type": "markdown", "metadata": {}, "source": ["A more complex operation could involve passing a list of operations to be applied to a specific column."]}, {"cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th></th>\n", "      <th colspan=\"3\" halign=\"left\">total_bill</th>\n", "      <th>tip</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th></th>\n", "      <th>min</th>\n", "      <th>max</th>\n", "      <th>count</th>\n", "      <th>max</th>\n", "    </tr>\n", "    <tr>\n", "      <th>smoker</th>\n", "      <th>day</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">Yes</th>\n", "      <th>Thur</th>\n", "      <td>10.34</td>\n", "      <td>43.11</td>\n", "      <td>17</td>\n", "      <td>5.00</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>5.75</td>\n", "      <td>40.17</td>\n", "      <td>15</td>\n", "      <td>4.73</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>3.07</td>\n", "      <td>50.81</td>\n", "      <td>42</td>\n", "      <td>10.00</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>7.25</td>\n", "      <td>45.35</td>\n", "      <td>19</td>\n", "      <td>6.50</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">No</th>\n", "      <th>Thur</th>\n", "      <td>7.51</td>\n", "      <td>41.19</td>\n", "      <td>45</td>\n", "      <td>6.70</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>12.46</td>\n", "      <td>22.75</td>\n", "      <td>4</td>\n", "      <td>3.50</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>7.25</td>\n", "      <td>48.33</td>\n", "      <td>45</td>\n", "      <td>9.00</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>8.77</td>\n", "      <td>48.17</td>\n", "      <td>57</td>\n", "      <td>6.00</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["            total_bill                 tip\n", "                   min    max count    max\n", "smoker day                                \n", "Yes    Thur      10.34  43.11    17   5.00\n", "       Fri        5.75  40.17    15   4.73\n", "       Sat        3.07  50.81    42  10.00\n", "       Sun        7.25  45.35    19   6.50\n", "No     Thur       7.51  41.19    45   6.70\n", "       Fri       12.46  22.75     4   3.50\n", "       Sat        7.25  48.33    45   9.00\n", "       Sun        8.77  48.17    57   6.00"]}, "execution_count": 24, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data.groupby(['smoker', 'day']).aggregate({'total_bill':['min','max','count'], 'tip':'max'})"]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Selecting a Subset of Columns\n", "For large datasets, it may be desirable to aggregate a specific column or only a subset of columns. As an example, we can group the data by `smoker` and compute mean for `tip` column as follows:"]}, {"cell_type": "code", "execution_count": 25, "metadata": {}, "outputs": [{"data": {"text/plain": ["smoker\n", "Yes    3.008710\n", "No     2.991854\n", "Name: tip, dtype: float64"]}, "execution_count": 25, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data.groupby(['smoker'])['tip'].mean()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Similarly, we can group the data by `smoker` and `day` columns, compute median for `tip` column."]}, {"cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [{"data": {"text/plain": ["smoker  day \n", "Yes     Thur    2.560\n", "        Fri     2.500\n", "        Sat     2.690\n", "        Sun     3.500\n", "No      Thur    2.180\n", "        Fri     3.125\n", "        Sat     2.750\n", "        Sun     3.020\n", "Name: tip, dtype: float64"]}, "execution_count": 26, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data.groupby(['smoker','day'])['tip'].median()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### Pivot Tables"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Pivot Table is a popular operation that is commonly used on tabular data in spreadsheets. The pivot table takes simple column-wise data as input, and groups the entries into a two-dimensional table that provides a multidimensional summarization of the data. Pivot Tables are essentially a multidimensional version of `GroupBy`. __Pandas__ includes a `pandas.pivot_table` function and `DataFrame` also has a `pivot_table` method.\n", "\n", "Seaborn library comes preloaded with some sample datasets. We will load the `titanic` dataset from seaborn for our analysis and look at some examples. "]}, {"cell_type": "code", "execution_count": 27, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>survived</th>\n", "      <th>pclass</th>\n", "      <th>sex</th>\n", "      <th>age</th>\n", "      <th>sibsp</th>\n", "      <th>parch</th>\n", "      <th>fare</th>\n", "      <th>embarked</th>\n", "      <th>class</th>\n", "      <th>who</th>\n", "      <th>adult_male</th>\n", "      <th>deck</th>\n", "      <th>embark_town</th>\n", "      <th>alive</th>\n", "      <th>alone</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>0</td>\n", "      <td>3</td>\n", "      <td>male</td>\n", "      <td>22.0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>7.2500</td>\n", "      <td>S</td>\n", "      <td>Third</td>\n", "      <td>man</td>\n", "      <td>True</td>\n", "      <td>NaN</td>\n", "      <td>Southampton</td>\n", "      <td>no</td>\n", "      <td>False</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>1</td>\n", "      <td>1</td>\n", "      <td>female</td>\n", "      <td>38.0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>71.2833</td>\n", "      <td>C</td>\n", "      <td>First</td>\n", "      <td>woman</td>\n", "      <td>False</td>\n", "      <td>C</td>\n", "      <td>Cherbourg</td>\n", "      <td>yes</td>\n", "      <td>False</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>1</td>\n", "      <td>3</td>\n", "      <td>female</td>\n", "      <td>26.0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>7.9250</td>\n", "      <td>S</td>\n", "      <td>Third</td>\n", "      <td>woman</td>\n", "      <td>False</td>\n", "      <td>NaN</td>\n", "      <td>Southampton</td>\n", "      <td>yes</td>\n", "      <td>True</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>1</td>\n", "      <td>1</td>\n", "      <td>female</td>\n", "      <td>35.0</td>\n", "      <td>1</td>\n", "      <td>0</td>\n", "      <td>53.1000</td>\n", "      <td>S</td>\n", "      <td>First</td>\n", "      <td>woman</td>\n", "      <td>False</td>\n", "      <td>C</td>\n", "      <td>Southampton</td>\n", "      <td>yes</td>\n", "      <td>False</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>0</td>\n", "      <td>3</td>\n", "      <td>male</td>\n", "      <td>35.0</td>\n", "      <td>0</td>\n", "      <td>0</td>\n", "      <td>8.0500</td>\n", "      <td>S</td>\n", "      <td>Third</td>\n", "      <td>man</td>\n", "      <td>True</td>\n", "      <td>NaN</td>\n", "      <td>Southampton</td>\n", "      <td>no</td>\n", "      <td>True</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   survived  pclass     sex   age  sibsp  parch     fare embarked  class  \\\n", "0         0       3    male  22.0      1      0   7.2500        S  Third   \n", "1         1       1  female  38.0      1      0  71.2833        C  First   \n", "2         1       3  female  26.0      0      0   7.9250        S  Third   \n", "3         1       1  female  35.0      1      0  53.1000        S  First   \n", "4         0       3    male  35.0      0      0   8.0500        S  Third   \n", "\n", "     who  adult_male deck  embark_town alive  alone  \n", "0    man        True  NaN  Southampton    no  False  \n", "1  woman       False    C    Cherbourg   yes  False  \n", "2  woman       False  NaN  Southampton   yes   True  \n", "3  woman       False    C  Southampton   yes  False  \n", "4    man        True  NaN  Southampton    no   True  "]}, "execution_count": 27, "metadata": {}, "output_type": "execute_result"}], "source": ["# Get Data\n", "titanic_df = sns.load_dataset('titanic')\n", "titanic_df.head()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Let's say we want to look at the average number of people that survived by both sex and class. We can get the results using both `GroupBy` and `pivot_table`."]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Using `GroupBy`"]}, {"cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th>class</th>\n", "      <th>First</th>\n", "      <th>Second</th>\n", "      <th>Third</th>\n", "    </tr>\n", "    <tr>\n", "      <th>sex</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>female</th>\n", "      <td>0.968085</td>\n", "      <td>0.921053</td>\n", "      <td>0.500000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>male</th>\n", "      <td>0.368852</td>\n", "      <td>0.157407</td>\n", "      <td>0.135447</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["class      First    Second     Third\n", "sex                                 \n", "female  0.968085  0.921053  0.500000\n", "male    0.368852  0.157407  0.135447"]}, "execution_count": 28, "metadata": {}, "output_type": "execute_result"}], "source": ["titanic_df.groupby(['sex', 'class'])['survived'].aggregate('mean').unstack()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Using `pivot_table`"]}, {"cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th>class</th>\n", "      <th>First</th>\n", "      <th>Second</th>\n", "      <th>Third</th>\n", "    </tr>\n", "    <tr>\n", "      <th>sex</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>female</th>\n", "      <td>0.968085</td>\n", "      <td>0.921053</td>\n", "      <td>0.500000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>male</th>\n", "      <td>0.368852</td>\n", "      <td>0.157407</td>\n", "      <td>0.135447</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["class      First    Second     Third\n", "sex                                 \n", "female  0.968085  0.921053  0.500000\n", "male    0.368852  0.157407  0.135447"]}, "execution_count": 29, "metadata": {}, "output_type": "execute_result"}], "source": ["titanic_df.pivot_table(values='survived', index='sex', columns='class')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["We can see that the `pivot_table` approach is much more readable than the `GroupBy` and produces the same result. The default aggregation operation is _mean_."]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Multi-level Pivot Table\n", "\n", "Just as in `GroupBy`, the data can be grouped at multiple levels using `pivot_table`. Suppose we want to group survival by `sex` and `age`. Since `age` is a continuous variable, we can create bins for `age` using `pd.cut` function and then group the data."]}, {"cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [{"data": {"text/plain": ["0      (18.0, 80.0]\n", "1      (18.0, 80.0]\n", "2      (18.0, 80.0]\n", "3      (18.0, 80.0]\n", "4      (18.0, 80.0]\n", "           ...     \n", "886    (18.0, 80.0]\n", "887    (18.0, 80.0]\n", "888             NaN\n", "889    (18.0, 80.0]\n", "890    (18.0, 80.0]\n", "Name: age, Length: 891, dtype: category\n", "Categories (2, interval[int64]): [(0, 18] < (18, 80]]"]}, "execution_count": 31, "metadata": {}, "output_type": "execute_result"}], "source": ["# Create bins for Age\n", "age = pd.cut(titanic_df['age'], bins=[0,18,80])\n", "age"]}, {"cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>class</th>\n", "      <th>First</th>\n", "      <th>Second</th>\n", "      <th>Third</th>\n", "    </tr>\n", "    <tr>\n", "      <th>sex</th>\n", "      <th>age</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">female</th>\n", "      <th>(0, 18]</th>\n", "      <td>0.909091</td>\n", "      <td>1.000000</td>\n", "      <td>0.511628</td>\n", "    </tr>\n", "    <tr>\n", "      <th>(18, 80]</th>\n", "      <td>0.972973</td>\n", "      <td>0.900000</td>\n", "      <td>0.423729</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">male</th>\n", "      <th>(0, 18]</th>\n", "      <td>0.800000</td>\n", "      <td>0.600000</td>\n", "      <td>0.215686</td>\n", "    </tr>\n", "    <tr>\n", "      <th>(18, 80]</th>\n", "      <td>0.375000</td>\n", "      <td>0.071429</td>\n", "      <td>0.133663</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["class               First    Second     Third\n", "sex    age                                   \n", "female (0, 18]   0.909091  1.000000  0.511628\n", "       (18, 80]  0.972973  0.900000  0.423729\n", "male   (0, 18]   0.800000  0.600000  0.215686\n", "       (18, 80]  0.375000  0.071429  0.133663"]}, "execution_count": 32, "metadata": {}, "output_type": "execute_result"}], "source": ["# Group data\n", "titanic_df.pivot_table(values='survived', index=['sex', age], columns='class')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The operation can be applied to columns in a similar fashion. Suppose we want to group survival by `sex` and `age` and look at the data by `class` and `fare`. \n", "\n", "We can discretize the `fare` variable into equal-sized buckets based on sample quantiles using `pd.qcut` and then group the data."]}, {"cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [], "source": ["# Create bins for fare\n", "fare = pd.qcut(titanic_df['fare'], q=2)"]}, {"cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>fare</th>\n", "      <th colspan=\"3\" halign=\"left\">(-0.001, 14.454]</th>\n", "      <th colspan=\"3\" halign=\"left\">(14.454, 512.329]</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>class</th>\n", "      <th>First</th>\n", "      <th>Second</th>\n", "      <th>Third</th>\n", "      <th>First</th>\n", "      <th>Second</th>\n", "      <th>Third</th>\n", "    </tr>\n", "    <tr>\n", "      <th>sex</th>\n", "      <th>age</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">female</th>\n", "      <th>(0, 18]</th>\n", "      <td>NaN</td>\n", "      <td>1.000000</td>\n", "      <td>0.714286</td>\n", "      <td>0.909091</td>\n", "      <td>1.000000</td>\n", "      <td>0.318182</td>\n", "    </tr>\n", "    <tr>\n", "      <th>(18, 80]</th>\n", "      <td>NaN</td>\n", "      <td>0.880000</td>\n", "      <td>0.444444</td>\n", "      <td>0.972973</td>\n", "      <td>0.914286</td>\n", "      <td>0.391304</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">male</th>\n", "      <th>(0, 18]</th>\n", "      <td>NaN</td>\n", "      <td>0.000000</td>\n", "      <td>0.260870</td>\n", "      <td>0.800000</td>\n", "      <td>0.818182</td>\n", "      <td>0.178571</td>\n", "    </tr>\n", "    <tr>\n", "      <th>(18, 80]</th>\n", "      <td>0.0</td>\n", "      <td>0.098039</td>\n", "      <td>0.125000</td>\n", "      <td>0.391304</td>\n", "      <td>0.030303</td>\n", "      <td>0.192308</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["fare            (-0.001, 14.454]                     (14.454, 512.329]  \\\n", "class                      First    Second     Third             First   \n", "sex    age                                                               \n", "female (0, 18]               NaN  1.000000  0.714286          0.909091   \n", "       (18, 80]              NaN  0.880000  0.444444          0.972973   \n", "male   (0, 18]               NaN  0.000000  0.260870          0.800000   \n", "       (18, 80]              0.0  0.098039  0.125000          0.391304   \n", "\n", "fare                                 \n", "class              Second     Third  \n", "sex    age                           \n", "female (0, 18]   1.000000  0.318182  \n", "       (18, 80]  0.914286  0.391304  \n", "male   (0, 18]   0.818182  0.178571  \n", "       (18, 80]  0.030303  0.192308  "]}, "execution_count": 34, "metadata": {}, "output_type": "execute_result"}], "source": ["# Group data\n", "titanic_df.pivot_table(values='survived', index=['sex', age], columns=[fare, 'class'])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Using `aggfunc`\n", "`aggfunc` keyword can be used to specify the aggregate functions that can be applied to different columns."]}, {"cell_type": "code", "execution_count": 35, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th colspan=\"3\" halign=\"left\">fare</th>\n", "      <th colspan=\"3\" halign=\"left\">survived</th>\n", "    </tr>\n", "    <tr>\n", "      <th>class</th>\n", "      <th>First</th>\n", "      <th>Second</th>\n", "      <th>Third</th>\n", "      <th>First</th>\n", "      <th>Second</th>\n", "      <th>Third</th>\n", "    </tr>\n", "    <tr>\n", "      <th>sex</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>female</th>\n", "      <td>106.125798</td>\n", "      <td>21.970121</td>\n", "      <td>16.118810</td>\n", "      <td>91</td>\n", "      <td>70</td>\n", "      <td>72</td>\n", "    </tr>\n", "    <tr>\n", "      <th>male</th>\n", "      <td>67.226127</td>\n", "      <td>19.741782</td>\n", "      <td>12.661633</td>\n", "      <td>45</td>\n", "      <td>17</td>\n", "      <td>47</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["              fare                       survived             \n", "class        First     Second      Third    First Second Third\n", "sex                                                           \n", "female  106.125798  21.970121  16.118810       91     70    72\n", "male     67.226127  19.741782  12.661633       45     17    47"]}, "execution_count": 35, "metadata": {}, "output_type": "execute_result"}], "source": ["titanic_df.pivot_table(index='sex', columns='class',\n", "                      aggfunc={'survived':'sum', 'fare': 'mean'})"]}, {"cell_type": "markdown", "metadata": {}, "source": ["To compute totals along each grouping, `margins` keyword can be used."]}, {"cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th>class</th>\n", "      <th>First</th>\n", "      <th>Second</th>\n", "      <th>Third</th>\n", "      <th>All</th>\n", "    </tr>\n", "    <tr>\n", "      <th>sex</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>female</th>\n", "      <td>91</td>\n", "      <td>70</td>\n", "      <td>72</td>\n", "      <td>233</td>\n", "    </tr>\n", "    <tr>\n", "      <th>male</th>\n", "      <td>45</td>\n", "      <td>17</td>\n", "      <td>47</td>\n", "      <td>109</td>\n", "    </tr>\n", "    <tr>\n", "      <th>All</th>\n", "      <td>136</td>\n", "      <td>87</td>\n", "      <td>119</td>\n", "      <td>342</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["class   First  Second  Third  All\n", "sex                              \n", "female     91      70     72  233\n", "male       45      17     47  109\n", "All       136      87    119  342"]}, "execution_count": 36, "metadata": {}, "output_type": "execute_result"}], "source": ["titanic_df.pivot_table(values='survived', index='sex', columns='class',\n", "                      aggfunc='sum', margins=True)"]}, {"attachments": {}, "cell_type": "markdown", "metadata": {}, "source": ["### `describe()` method\n", "Convenience methods, such as `describe()`, can be used to compute several common aggregates for each column. It also comes in handy when you are trying to understand the overall properties of a dataset."]}, {"cell_type": "code", "execution_count": 38, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>total_bill</th>\n", "      <th>tip</th>\n", "      <th>size</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>count</th>\n", "      <td>244.000000</td>\n", "      <td>244.000000</td>\n", "      <td>244.000000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>mean</th>\n", "      <td>19.785943</td>\n", "      <td>2.998279</td>\n", "      <td>2.569672</td>\n", "    </tr>\n", "    <tr>\n", "      <th>std</th>\n", "      <td>8.902412</td>\n", "      <td>1.383638</td>\n", "      <td>0.951100</td>\n", "    </tr>\n", "    <tr>\n", "      <th>min</th>\n", "      <td>3.070000</td>\n", "      <td>1.000000</td>\n", "      <td>1.000000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>25%</th>\n", "      <td>13.347500</td>\n", "      <td>2.000000</td>\n", "      <td>2.000000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>50%</th>\n", "      <td>17.795000</td>\n", "      <td>2.900000</td>\n", "      <td>2.000000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>75%</th>\n", "      <td>24.127500</td>\n", "      <td>3.562500</td>\n", "      <td>3.000000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>max</th>\n", "      <td>50.810000</td>\n", "      <td>10.000000</td>\n", "      <td>6.000000</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["       total_bill         tip        size\n", "count  244.000000  244.000000  244.000000\n", "mean    19.785943    2.998279    2.569672\n", "std      8.902412    1.383638    0.951100\n", "min      3.070000    1.000000    1.000000\n", "25%     13.347500    2.000000    2.000000\n", "50%     17.795000    2.900000    2.000000\n", "75%     24.127500    3.562500    3.000000\n", "max     50.810000   10.000000    6.000000"]}, "execution_count": 38, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data.describe()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### `describe()` on GroupBy\n", "`describe()` can be used on a `groupby()` object to get common aggregates for a subset of data."]}, {"cell_type": "code", "execution_count": 39, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th></th>\n", "      <th>count</th>\n", "      <th>mean</th>\n", "      <th>std</th>\n", "      <th>min</th>\n", "      <th>25%</th>\n", "      <th>50%</th>\n", "      <th>75%</th>\n", "      <th>max</th>\n", "    </tr>\n", "    <tr>\n", "      <th>smoker</th>\n", "      <th>day</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">Yes</th>\n", "      <th>Thur</th>\n", "      <td>17.0</td>\n", "      <td>19.190588</td>\n", "      <td>8.355149</td>\n", "      <td>10.34</td>\n", "      <td>13.510</td>\n", "      <td>16.470</td>\n", "      <td>19.8100</td>\n", "      <td>43.11</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>15.0</td>\n", "      <td>16.813333</td>\n", "      <td>9.086388</td>\n", "      <td>5.75</td>\n", "      <td>11.690</td>\n", "      <td>13.420</td>\n", "      <td>18.6650</td>\n", "      <td>40.17</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>42.0</td>\n", "      <td>21.276667</td>\n", "      <td>10.069138</td>\n", "      <td>3.07</td>\n", "      <td>13.405</td>\n", "      <td>20.390</td>\n", "      <td>26.7925</td>\n", "      <td>50.81</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>19.0</td>\n", "      <td>24.120000</td>\n", "      <td>10.442511</td>\n", "      <td>7.25</td>\n", "      <td>17.165</td>\n", "      <td>23.100</td>\n", "      <td>32.3750</td>\n", "      <td>45.35</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"4\" valign=\"top\">No</th>\n", "      <th>Thur</th>\n", "      <td>45.0</td>\n", "      <td>17.113111</td>\n", "      <td>7.721728</td>\n", "      <td>7.51</td>\n", "      <td>11.690</td>\n", "      <td>15.950</td>\n", "      <td>20.2700</td>\n", "      <td>41.19</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Fri</th>\n", "      <td>4.0</td>\n", "      <td>18.420000</td>\n", "      <td>5.059282</td>\n", "      <td>12.46</td>\n", "      <td>15.100</td>\n", "      <td>19.235</td>\n", "      <td>22.5550</td>\n", "      <td>22.75</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sat</th>\n", "      <td>45.0</td>\n", "      <td>19.661778</td>\n", "      <td>8.939181</td>\n", "      <td>7.25</td>\n", "      <td>14.730</td>\n", "      <td>17.820</td>\n", "      <td>20.6500</td>\n", "      <td>48.33</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Sun</th>\n", "      <td>57.0</td>\n", "      <td>20.506667</td>\n", "      <td>8.130189</td>\n", "      <td>8.77</td>\n", "      <td>14.780</td>\n", "      <td>18.430</td>\n", "      <td>25.0000</td>\n", "      <td>48.17</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["             count       mean        std    min     25%     50%      75%  \\\n", "smoker day                                                                 \n", "Yes    Thur   17.0  19.190588   8.355149  10.34  13.510  16.470  19.8100   \n", "       Fri    15.0  16.813333   9.086388   5.75  11.690  13.420  18.6650   \n", "       Sat    42.0  21.276667  10.069138   3.07  13.405  20.390  26.7925   \n", "       Sun    19.0  24.120000  10.442511   7.25  17.165  23.100  32.3750   \n", "No     Thur   45.0  17.113111   7.721728   7.51  11.690  15.950  20.2700   \n", "       Fri     4.0  18.420000   5.059282  12.46  15.100  19.235  22.5550   \n", "       Sat    45.0  19.661778   8.939181   7.25  14.730  17.820  20.6500   \n", "       Sun    57.0  20.506667   8.130189   8.77  14.780  18.430  25.0000   \n", "\n", "               max  \n", "smoker day          \n", "Yes    Thur  43.11  \n", "       Fri   40.17  \n", "       Sat   50.81  \n", "       Sun   45.35  \n", "No     Thur  41.19  \n", "       Fri   22.75  \n", "       Sat   48.33  \n", "       Sun   48.17  "]}, "execution_count": 39, "metadata": {}, "output_type": "execute_result"}], "source": ["tips_data.groupby(['smoker','day'])['total_bill'].describe()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Combining Data"]}, {"attachments": {}, "cell_type": "markdown", "metadata": {}, "source": ["Data science workflows often involve combining data from different sources to enhance the analysis. There are multiple ways in which data can be combined ranging from the straightforward concatenation of two different datasets, to more complicated database-style joins.\n", "\n", "- [pandas.concat](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html) - stacks together objects along an axis.\n", "- [DataFrame.append](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.append.html) - works similar to `pandas.concat` but does not modify the original object. It creates a new object with the combined data. \n", "- [pandas.merge](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html) - joins rows in DataFrame based on one or more keys. It works as the entry point for all standard database join operations between `DataFrame` or `Series` objects.\n", "- [DataFrame.join](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html) - `join` instance of a DataFrame can be used for merging by index. It can be used to combine many DataFrame objects with same or similar indexes but non-overlapping columns."]}, {"cell_type": "markdown", "metadata": {}, "source": ["### `concat()`\n", " `concat()` can be used to stack data frames together along an axis."]}, {"cell_type": "code", "execution_count": 40, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["df2 is: \n", "    one  two\n", "a    0    1\n", "b    2    3\n", "c    4    5\n", "\n", "df3 is: \n", "    three  four\n", "a      5     6\n", "c      7     8\n", "\n", "df4 is: \n", "    one  two\n", "a    5    6\n", "c    7    8\n"]}], "source": ["# Create Data\n", "df2 = pd.DataFrame(np.arange(6).reshape(3, 2), index=['a', 'b', 'c'],\n", "                   columns=['one', 'two'])\n", "print('df2 is: \\n', df2)\n", "\n", "df3 = pd.DataFrame(5 + np.arange(4).reshape(2, 2), index=['a', 'c'],\n", "                   columns=['three', 'four'])\n", "print('\\ndf3 is: \\n', df3)\n", "\n", "df4 = pd.DataFrame(5 + np.arange(4).reshape(2, 2), index=['a', 'c'],\n", "                   columns=['one', 'two'])\n", "print('\\ndf4 is: \\n', df4)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["By default, `concat()` works row-wise within the `DataFrame` (along axis=0)."]}, {"cell_type": "code", "execution_count": 41, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>one</th>\n", "      <th>two</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>a</th>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>b</th>\n", "      <td>2</td>\n", "      <td>3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>c</th>\n", "      <td>4</td>\n", "      <td>5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>a</th>\n", "      <td>5</td>\n", "      <td>6</td>\n", "    </tr>\n", "    <tr>\n", "      <th>c</th>\n", "      <td>7</td>\n", "      <td>8</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   one  two\n", "a    0    1\n", "b    2    3\n", "c    4    5\n", "a    5    6\n", "c    7    8"]}, "execution_count": 41, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.concat([df2,df4])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["`ignore_index` flag can be used to ignore the index when it is not necessary."]}, {"cell_type": "code", "execution_count": 42, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>one</th>\n", "      <th>two</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>2</td>\n", "      <td>3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>4</td>\n", "      <td>5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>5</td>\n", "      <td>6</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>7</td>\n", "      <td>8</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   one  two\n", "0    0    1\n", "1    2    3\n", "2    4    5\n", "3    5    6\n", "4    7    8"]}, "execution_count": 42, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.concat([df2,df4], ignore_index=True)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["You an specify an __axis__ along which the concatenation should take place. If `axis` parameter is not specified, the concatenation works row-wise generating `NaN` values for unmatched columns."]}, {"cell_type": "code", "execution_count": 43, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>one</th>\n", "      <th>two</th>\n", "      <th>three</th>\n", "      <th>four</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>a</th>\n", "      <td>0.0</td>\n", "      <td>1.0</td>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "    <tr>\n", "      <th>b</th>\n", "      <td>2.0</td>\n", "      <td>3.0</td>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "    <tr>\n", "      <th>c</th>\n", "      <td>4.0</td>\n", "      <td>5.0</td>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "    <tr>\n", "      <th>a</th>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "      <td>5.0</td>\n", "      <td>6.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>c</th>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "      <td>7.0</td>\n", "      <td>8.0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   one  two  three  four\n", "a  0.0  1.0    NaN   NaN\n", "b  2.0  3.0    NaN   NaN\n", "c  4.0  5.0    NaN   NaN\n", "a  NaN  NaN    5.0   6.0\n", "c  NaN  NaN    7.0   8.0"]}, "execution_count": 43, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.concat([df2,df3])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["When `axis='columns'` is specified, the concatenation works along columns."]}, {"cell_type": "code", "execution_count": 44, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>one</th>\n", "      <th>two</th>\n", "      <th>three</th>\n", "      <th>four</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>a</th>\n", "      <td>0</td>\n", "      <td>1</td>\n", "      <td>5.0</td>\n", "      <td>6.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>b</th>\n", "      <td>2</td>\n", "      <td>3</td>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "    <tr>\n", "      <th>c</th>\n", "      <td>4</td>\n", "      <td>5</td>\n", "      <td>7.0</td>\n", "      <td>8.0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   one  two  three  four\n", "a    0    1    5.0   6.0\n", "b    2    3    NaN   NaN\n", "c    4    5    7.0   8.0"]}, "execution_count": 44, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.concat([df2,df3], axis='columns')"]}, {"attachments": {}, "cell_type": "markdown", "metadata": {}, "source": ["### `append()`\n", "`append()` works similar to `concat()` but does not modify the original object. It creates a new object with the combined data. The method works row-wise within the DataFrame (along axis=0). This method is not very efficient, as it involves the creation of a new index and data buffer."]}, {"cell_type": "code", "execution_count": 45, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>one</th>\n", "      <th>two</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>a</th>\n", "      <td>0</td>\n", "      <td>1</td>\n", "    </tr>\n", "    <tr>\n", "      <th>b</th>\n", "      <td>2</td>\n", "      <td>3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>c</th>\n", "      <td>4</td>\n", "      <td>5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>a</th>\n", "      <td>5</td>\n", "      <td>6</td>\n", "    </tr>\n", "    <tr>\n", "      <th>c</th>\n", "      <td>7</td>\n", "      <td>8</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   one  two\n", "a    0    1\n", "b    2    3\n", "c    4    5\n", "a    5    6\n", "c    7    8"]}, "execution_count": 45, "metadata": {}, "output_type": "execute_result"}], "source": ["df2.append(df4)"]}, {"cell_type": "code", "execution_count": 46, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>one</th>\n", "      <th>two</th>\n", "      <th>three</th>\n", "      <th>four</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>a</th>\n", "      <td>0.0</td>\n", "      <td>1.0</td>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "    <tr>\n", "      <th>b</th>\n", "      <td>2.0</td>\n", "      <td>3.0</td>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "    <tr>\n", "      <th>c</th>\n", "      <td>4.0</td>\n", "      <td>5.0</td>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "    <tr>\n", "      <th>a</th>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "      <td>5.0</td>\n", "      <td>6.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>c</th>\n", "      <td>NaN</td>\n", "      <td>NaN</td>\n", "      <td>7.0</td>\n", "      <td>8.0</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   one  two  three  four\n", "a  0.0  1.0    NaN   NaN\n", "b  2.0  3.0    NaN   NaN\n", "c  4.0  5.0    NaN   NaN\n", "a  NaN  NaN    5.0   6.0\n", "c  NaN  NaN    7.0   8.0"]}, "execution_count": 46, "metadata": {}, "output_type": "execute_result"}], "source": ["df2.append(df3)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### `merge()`\n", "`merge()` joins rows in DataFrame based on one or more keys. It works as the entry point for all standard database join operations. Let's create sample data and look at some examples."]}, {"cell_type": "code", "execution_count": 47, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["dept_df is: \n", "   employee        group\n", "0     John   Accounting\n", "1     Jake  Engineering\n", "2     Jane  Engineering\n", "3     Suzi   Management\n", "4     Chad    Marketing\n", "\n", "skills_df is: \n", "          group        skills\n", "0   Accounting          math\n", "1   Accounting  spreadsheets\n", "2  Engineering        coding\n", "3  Engineering         linux\n", "4   Management  spreadsheets\n", "5   Management  organization\n", "6   Operations           SAP\n"]}], "source": ["# Create data\n", "dept_df = pd.DataFrame({'employee': ['John', 'Jake', 'Jane', 'Suzi', 'Chad'],\n", "                    'group': ['Accounting', 'Engineering', 'Engineering', 'Management', 'Marketing']})\n", "print('dept_df is: \\n', dept_df)\n", "\n", "skills_df = pd.DataFrame({'group': ['Accounting', 'Accounting',\n", "                              'Engineering', 'Engineering', 'Management', 'Management', 'Operations'],\n", "                    'skills': ['math', 'spreadsheets', 'coding', 'linux',\n", "                               'spreadsheets', 'organization', 'SAP']})\n", "print('\\nskills_df is: \\n', skills_df)"]}, {"cell_type": "code", "execution_count": 48, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>group</th>\n", "      <th>skills</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>math</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>organization</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  employee        group        skills\n", "0     John   Accounting          math\n", "1     John   Accounting  spreadsheets\n", "2     Jake  Engineering        coding\n", "3     Jake  Engineering         linux\n", "4     Jane  Engineering        coding\n", "5     Jane  Engineering         linux\n", "6     Suzi   Management  spreadsheets\n", "7     Suzi   Management  organization"]}, "execution_count": 48, "metadata": {}, "output_type": "execute_result"}], "source": ["# Apply merge\n", "pd.merge(dept_df,skills_df)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Notice that a column to _join_ the data was not specified. `merge()` uses the overlapping column names as keys for joining data. It is a good practice to explicitly specify the column to join the data using `on` keyword. 'Marketing' and 'Operations' values and associated data are missing from the result as the operation returns only common set."]}, {"cell_type": "code", "execution_count": 49, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>group</th>\n", "      <th>skills</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>math</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>organization</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  employee        group        skills\n", "0     John   Accounting          math\n", "1     John   Accounting  spreadsheets\n", "2     Jake  Engineering        coding\n", "3     Jake  Engineering         linux\n", "4     Jane  Engineering        coding\n", "5     Jane  Engineering         linux\n", "6     Suzi   Management  spreadsheets\n", "7     Suzi   Management  organization"]}, "execution_count": 49, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.merge(dept_df,skills_df, on='group')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["If the column names are different in the `DataFrame`, then `left_on` and `right_on` keywords can be used."]}, {"cell_type": "code", "execution_count": 50, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>name</th>\n", "      <th>salary</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>John</td>\n", "      <td>70000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>Jake</td>\n", "      <td>80000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Jane</td>\n", "      <td>120000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Suzi</td>\n", "      <td>65000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Chad</td>\n", "      <td>90000</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["   name  salary\n", "0  John   70000\n", "1  Jake   80000\n", "2  Jane  120000\n", "3  Suzi   65000\n", "4  Chad   90000"]}, "execution_count": 50, "metadata": {}, "output_type": "execute_result"}], "source": ["# Create data\n", "emp_df = pd.DataFrame({'name': ['John', 'Jake', 'Jane', 'Suzi', 'Chad'],\n", "                    'salary': [70000, 80000, 120000, 65000, 90000]})\n", "emp_df"]}, {"cell_type": "code", "execution_count": 51, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>group</th>\n", "      <th>name</th>\n", "      <th>salary</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>John</td>\n", "      <td>70000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>Jake</td>\n", "      <td>80000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>Jane</td>\n", "      <td>120000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>Suzi</td>\n", "      <td>65000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Chad</td>\n", "      <td>Marketing</td>\n", "      <td>Chad</td>\n", "      <td>90000</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  employee        group  name  salary\n", "0     John   Accounting  John   70000\n", "1     Jake  Engineering  Jake   80000\n", "2     Jane  Engineering  Jane  120000\n", "3     Suzi   Management  Suzi   65000\n", "4     Chad    Marketing  Chad   90000"]}, "execution_count": 51, "metadata": {}, "output_type": "execute_result"}], "source": ["# Merge\n", "pd.merge(dept_df, emp_df, left_on='employee', right_on='name')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The redundant column can be dropped as needed using the `drop()` method."]}, {"cell_type": "code", "execution_count": 52, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>group</th>\n", "      <th>salary</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>70000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>80000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>120000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>65000</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Chad</td>\n", "      <td>Marketing</td>\n", "      <td>90000</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  employee        group  salary\n", "0     John   Accounting   70000\n", "1     Jake  Engineering   80000\n", "2     Jane  Engineering  120000\n", "3     Suzi   Management   65000\n", "4     Chad    Marketing   90000"]}, "execution_count": 52, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.merge(dept_df, emp_df, left_on='employee', right_on='name').drop('name', axis='columns')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Inner Join\n", "By default `merge()` performs an __inner__ join. The result is an intersection, or the common set found in both `DataFrame`. The merge operations we just saw were all __inner__ joins. Different join types such as `left, right, outer` can be specified using the `how=` parameter."]}, {"cell_type": "code", "execution_count": 53, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>group</th>\n", "      <th>skills</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>math</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>organization</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  employee        group        skills\n", "0     John   Accounting          math\n", "1     John   Accounting  spreadsheets\n", "2     Jake  Engineering        coding\n", "3     Jake  Engineering         linux\n", "4     Jane  Engineering        coding\n", "5     Jane  Engineering         linux\n", "6     Suzi   Management  spreadsheets\n", "7     Suzi   Management  organization"]}, "execution_count": 53, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.merge(dept_df,skills_df, on='group', how='inner')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["'Marketing' and 'Operations' values and associated data are missing from the result, as the operation returns only common set. "]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Left Join\n", "This join returns all records from the left DataFrame and the matched records from the right DataFrame."]}, {"cell_type": "code", "execution_count": 54, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>group</th>\n", "      <th>skills</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>math</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>organization</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>Chad</td>\n", "      <td>Marketing</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  employee        group        skills\n", "0     John   Accounting          math\n", "1     John   Accounting  spreadsheets\n", "2     Jake  Engineering        coding\n", "3     Jake  Engineering         linux\n", "4     Jane  Engineering        coding\n", "5     Jane  Engineering         linux\n", "6     Suzi   Management  spreadsheets\n", "7     Suzi   Management  organization\n", "8     Chad    Marketing           NaN"]}, "execution_count": 54, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.merge(dept_df,skills_df, on='group', how='left')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The result is a Cartesian product of the rows. Since there were two 'Engineering' rows in the left DataFrame and two in the right, there are four 'Engineering' rows in the result. Similarly, there was one 'Accounting' row in left DataFrame and two in the right, resulting in two 'Accounting' rows. The row for 'Operations' is missing as this join only keeps matched rows from right DataFrame."]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Right Join\n", "This join returns all records from the right DataFrame and the matched records from the left DataFrame."]}, {"cell_type": "code", "execution_count": 55, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>group</th>\n", "      <th>skills</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>math</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>organization</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>NaN</td>\n", "      <td>Operations</td>\n", "      <td>SAP</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  employee        group        skills\n", "0     John   Accounting          math\n", "1     John   Accounting  spreadsheets\n", "2     Jake  Engineering        coding\n", "3     Jane  Engineering        coding\n", "4     Jake  Engineering         linux\n", "5     Jane  Engineering         linux\n", "6     Suzi   Management  spreadsheets\n", "7     Suzi   Management  organization\n", "8      NaN   Operations           SAP"]}, "execution_count": 55, "metadata": {}, "output_type": "execute_result"}], "source": ["pd.merge(dept_df,skills_df, on='group', how='right')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The result is a Cartesian product of the rows. Since there were two 'Engineering' rows in the right DataFrame and two in the left, there are four 'Engineering' rows in the result. Similarly, there was one 'Accounting' row in left DataFrame and two in the right, resulting in two 'Accounting' rows. The row for 'Marketing' is missing as this join only keeps matched rows from right DataFrame."]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Outer Join\n", "The outer join takes the union of the keys, combining the effect of applying both left and right joins."]}, {"cell_type": "code", "execution_count": 56, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>group</th>\n", "      <th>skills</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>0</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>math</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>John</td>\n", "      <td>Accounting</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>3</th>\n", "      <td>Jake</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>4</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>5</th>\n", "      <td>Jane</td>\n", "      <td>Engineering</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>6</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>7</th>\n", "      <td>Suzi</td>\n", "      <td>Management</td>\n", "      <td>organization</td>\n", "    </tr>\n", "    <tr>\n", "      <th>8</th>\n", "      <td>Chad</td>\n", "      <td>Marketing</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "    <tr>\n", "      <th>9</th>\n", "      <td>NaN</td>\n", "      <td>Operations</td>\n", "      <td>SAP</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["  employee        group        skills\n", "0     John   Accounting          math\n", "1     John   Accounting  spreadsheets\n", "2     Jake  Engineering        coding\n", "3     Jake  Engineering         linux\n", "4     Jane  Engineering        coding\n", "5     Jane  Engineering         linux\n", "6     Suzi   Management  spreadsheets\n", "7     Suzi   Management  organization\n", "8     Chad    Marketing           NaN\n", "9      NaN   Operations           SAP"]}, "execution_count": 56, "metadata": {}, "output_type": "execute_result"}], "source": ["out_df = pd.merge(dept_df,skills_df, on='group', how='outer')\n", "out_df"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The result is a Cartesian product of the rows using all key combinations filling in all missing values with NAs."]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Using __index__ to merge\n", "Index can also be used as the key for merging by specifying the left_index and/or right_index flags."]}, {"cell_type": "code", "execution_count": 57, "metadata": {}, "outputs": [{"name": "stdout", "output_type": "stream", "text": ["dept_dfa is \n", "             employee\n", "group               \n", "Accounting      John\n", "Engineering     Jake\n", "Engineering     Jane\n", "Management      Suzi\n", "Marketing       Chad\n", "\n", "skills_dfa is \n", "                    skills\n", "group                    \n", "Accounting           math\n", "Accounting   spreadsheets\n", "Engineering        coding\n", "Engineering         linux\n", "Management   spreadsheets\n", "Management   organization\n", "Operations            SAP\n"]}], "source": ["# Create data\n", "dept_dfa = dept_df.set_index('group')\n", "print('dept_dfa is \\n', dept_dfa)\n", "skills_dfa = skills_df.set_index('group')\n", "print('\\nskills_dfa is \\n', skills_dfa)"]}, {"cell_type": "code", "execution_count": 58, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>skills</th>\n", "    </tr>\n", "    <tr>\n", "      <th>group</th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>Accounting</th>\n", "      <td>John</td>\n", "      <td>math</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Accounting</th>\n", "      <td>John</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jake</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jake</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jane</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jane</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Management</th>\n", "      <td>Suzi</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Management</th>\n", "      <td>Suzi</td>\n", "      <td>organization</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["            employee        skills\n", "group                             \n", "Accounting      John          math\n", "Accounting      John  spreadsheets\n", "Engineering     Jake        coding\n", "Engineering     Jake         linux\n", "Engineering     Jane        coding\n", "Engineering     Jane         linux\n", "Management      Suzi  spreadsheets\n", "Management      Suzi  organization"]}, "execution_count": 58, "metadata": {}, "output_type": "execute_result"}], "source": ["# Merge\n", "pd.merge(dept_dfa, skills_dfa, left_index=True, right_index=True)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### `join()`\n", "`join` instance of a DataFrame can also be used for merging by index. The `how` keword can be specified for the type of join."]}, {"cell_type": "code", "execution_count": 59, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>skills</th>\n", "    </tr>\n", "    <tr>\n", "      <th>group</th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>Accounting</th>\n", "      <td>John</td>\n", "      <td>math</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Accounting</th>\n", "      <td>John</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jake</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jake</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jane</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jane</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Management</th>\n", "      <td>Suzi</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Management</th>\n", "      <td>Suzi</td>\n", "      <td>organization</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["            employee        skills\n", "group                             \n", "Accounting      John          math\n", "Accounting      John  spreadsheets\n", "Engineering     Jake        coding\n", "Engineering     Jake         linux\n", "Engineering     Jane        coding\n", "Engineering     Jane         linux\n", "Management      Suzi  spreadsheets\n", "Management      Suzi  organization"]}, "execution_count": 59, "metadata": {}, "output_type": "execute_result"}], "source": ["dept_dfa.join(skills_dfa, how='inner')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["'Marketing' and 'Operations' values and associated data are missing from the result, as the operation returns only common set."]}, {"cell_type": "code", "execution_count": 60, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>employee</th>\n", "      <th>skills</th>\n", "    </tr>\n", "    <tr>\n", "      <th>group</th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>Accounting</th>\n", "      <td>John</td>\n", "      <td>math</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Accounting</th>\n", "      <td>John</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jake</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jake</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jane</td>\n", "      <td>coding</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Engineering</th>\n", "      <td>Jane</td>\n", "      <td>linux</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Management</th>\n", "      <td>Suzi</td>\n", "      <td>spreadsheets</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Management</th>\n", "      <td>Suzi</td>\n", "      <td>organization</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Marketing</th>\n", "      <td>Chad</td>\n", "      <td>NaN</td>\n", "    </tr>\n", "    <tr>\n", "      <th>Operations</th>\n", "      <td>NaN</td>\n", "      <td>SAP</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["            employee        skills\n", "group                             \n", "Accounting      John          math\n", "Accounting      John  spreadsheets\n", "Engineering     Jake        coding\n", "Engineering     Jake         linux\n", "Engineering     Jane        coding\n", "Engineering     Jane         linux\n", "Management      Suzi  spreadsheets\n", "Management      Suzi  organization\n", "Marketing       Chad           NaN\n", "Operations       NaN           SAP"]}, "execution_count": 60, "metadata": {}, "output_type": "execute_result"}], "source": ["dept_dfa.join(skills_dfa, how='outer')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The result shows all data, filling in the missing values with NAs."]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Hierarchical Indexing"]}, {"attachments": {}, "cell_type": "markdown", "metadata": {}, "source": ["Hierarchical indexing (also known as multi-indexing) allows you to have multiple (two or more) index levels within a single index on an axis. It provides a way for representing higher dimensional data in a lower dimensional form. Let's start with a simple example, creating a series with multi-index."]}, {"cell_type": "markdown", "metadata": {}, "source": ["### Multi-indexed Series"]}, {"cell_type": "code", "execution_count": 61, "metadata": {}, "outputs": [{"data": {"text/plain": ["(CA, 2005)    33871648\n", "(CA, 2015)    37253956\n", "(NY, 2005)    18976457\n", "(NY, 2015)    19378102\n", "(TX, 2005)    20851820\n", "(TX, 2015)    25145561\n", "dtype: int64"]}, "execution_count": 61, "metadata": {}, "output_type": "execute_result"}], "source": ["# Create Data\n", "index = [('CA', 2005), ('CA', 2015),\n", "         ('NY', 2005), ('NY', 2015),\n", "         ('TX', 2005), ('TX', 2015)]\n", "population = [33871648, 37253956,\n", "               18976457, 19378102,\n", "               20851820, 25145561]\n", "pop_series = pd.Series(population, index=index)\n", "pop_series"]}, {"cell_type": "markdown", "metadata": {}, "source": ["With this indexing, you can easily index or slice the series using the index. However, what if you wanted to select all the values for 2015? The tuple-based index is essentially a multi-index and __Pandas__ `MultiIndex` type allows us to create multi-level indexes. This provides us with the flexibility to perform operations on indexes easily and efficiently.\n", "\n", "We will create a multi-indexed index for `pop_series` using the `MultiIndex` type."]}, {"cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [{"data": {"text/plain": ["FrozenList([['CA', 'NY', 'TX'], [2005, 2015]])"]}, "execution_count": 62, "metadata": {}, "output_type": "execute_result"}], "source": ["new_index = pd.MultiIndex.from_tuples(index)\n", "new_index.levels"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Notice that the `new_index` object contains multiple levels of indexing, the state names and the years. We can now `reindex` the `pop_series` to see hierarchical representation of the data."]}, {"cell_type": "code", "execution_count": 63, "metadata": {}, "outputs": [{"data": {"text/plain": ["CA  2005    33871648\n", "    2015    37253956\n", "NY  2005    18976457\n", "    2015    19378102\n", "TX  2005    20851820\n", "    2015    25145561\n", "dtype: int64"]}, "execution_count": 63, "metadata": {}, "output_type": "execute_result"}], "source": ["pop_series = pop_series.reindex(new_index)\n", "pop_series"]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Subset Selection\n", "Subset selection of multi-indexed data is similar to what we have seen in the previous part of this guide series. Let's take a quick look."]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Select data for California"]}, {"cell_type": "code", "execution_count": 64, "metadata": {}, "outputs": [{"data": {"text/plain": ["2005    33871648\n", "2015    37253956\n", "dtype: int64"]}, "execution_count": 64, "metadata": {}, "output_type": "execute_result"}], "source": ["pop_series['CA']"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Select data from New York to Texas"]}, {"cell_type": "code", "execution_count": 65, "metadata": {}, "outputs": [{"data": {"text/plain": ["NY  2005    18976457\n", "    2015    19378102\n", "TX  2005    20851820\n", "    2015    25145561\n", "dtype: int64"]}, "execution_count": 65, "metadata": {}, "output_type": "execute_result"}], "source": ["pop_series['NY':'TX']"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Select data for California and Texas"]}, {"cell_type": "code", "execution_count": 66, "metadata": {}, "outputs": [{"data": {"text/plain": ["CA  2005    33871648\n", "    2015    37253956\n", "TX  2005    20851820\n", "    2015    25145561\n", "dtype: int64"]}, "execution_count": 66, "metadata": {}, "output_type": "execute_result"}], "source": ["pop_series[['CA','TX']]"]}, {"cell_type": "markdown", "metadata": {}, "source": ["We can now easily access the data for second index and answer our question about selecting all the values for 2015."]}, {"cell_type": "code", "execution_count": 67, "metadata": {}, "outputs": [{"data": {"text/plain": ["CA    37253956\n", "NY    19378102\n", "TX    25145561\n", "dtype: int64"]}, "execution_count": 67, "metadata": {}, "output_type": "execute_result"}], "source": ["pop_series[:,2015]"]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### `unstack()` and `stack()`\n", "\n", "`unstack()` method will convert a multi-indexed Series into a DataFrame, and naturally `stack()` would do the opposite."]}, {"cell_type": "code", "execution_count": 68, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>2005</th>\n", "      <th>2015</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>CA</th>\n", "      <td>33871648</td>\n", "      <td>37253956</td>\n", "    </tr>\n", "    <tr>\n", "      <th>NY</th>\n", "      <td>18976457</td>\n", "      <td>19378102</td>\n", "    </tr>\n", "    <tr>\n", "      <th>TX</th>\n", "      <td>20851820</td>\n", "      <td>25145561</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["        2005      2015\n", "CA  33871648  37253956\n", "NY  18976457  19378102\n", "TX  20851820  25145561"]}, "execution_count": 68, "metadata": {}, "output_type": "execute_result"}], "source": ["# Unstack\n", "pop_df = pop_series.unstack()\n", "pop_df"]}, {"cell_type": "markdown", "metadata": {}, "source": ["The result is a DataFrame where second level index (years) is converted to columns, and first level index (states) remains as the index of the DataFrame."]}, {"cell_type": "code", "execution_count": 69, "metadata": {}, "outputs": [{"data": {"text/plain": ["CA  2005    33871648\n", "    2015    37253956\n", "NY  2005    18976457\n", "    2015    19378102\n", "TX  2005    20851820\n", "    2015    25145561\n", "dtype: int64"]}, "execution_count": 69, "metadata": {}, "output_type": "execute_result"}], "source": ["# Stack\n", "pop_df.stack()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["`level` can be specified to `unstack()` by a specific index level. Specifying `level=0` will unstack based on the outermost index level i.e. by 'state'. "]}, {"cell_type": "code", "execution_count": 70, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>CA</th>\n", "      <th>NY</th>\n", "      <th>TX</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>2005</th>\n", "      <td>33871648</td>\n", "      <td>18976457</td>\n", "      <td>20851820</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2015</th>\n", "      <td>37253956</td>\n", "      <td>19378102</td>\n", "      <td>25145561</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["            CA        NY        TX\n", "2005  33871648  18976457  20851820\n", "2015  37253956  19378102  25145561"]}, "execution_count": 70, "metadata": {}, "output_type": "execute_result"}], "source": ["pop_series.unstack(level=0)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Specifying `level=1` unstacks by the inner index, in this case 'year'."]}, {"cell_type": "code", "execution_count": 71, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th></th>\n", "      <th>2005</th>\n", "      <th>2015</th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>CA</th>\n", "      <td>33871648</td>\n", "      <td>37253956</td>\n", "    </tr>\n", "    <tr>\n", "      <th>NY</th>\n", "      <td>18976457</td>\n", "      <td>19378102</td>\n", "    </tr>\n", "    <tr>\n", "      <th>TX</th>\n", "      <td>20851820</td>\n", "      <td>25145561</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["        2005      2015\n", "CA  33871648  37253956\n", "NY  18976457  19378102\n", "TX  20851820  25145561"]}, "execution_count": 71, "metadata": {}, "output_type": "execute_result"}], "source": ["pop_series.unstack(level=1)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["### Multi-indexed DataFrame"]}, {"cell_type": "markdown", "metadata": {}, "source": ["In a `DataFrame`, both rows and columns can have multiple levels of indices. Let's create some sample data and take a look."]}, {"cell_type": "code", "execution_count": 72, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2019</th>\n", "      <th>2</th>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "      <td>48.0</td>\n", "      <td>54.5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "      <td>67.0</td>\n", "      <td>52.9</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2018</th>\n", "      <th>2</th>\n", "      <td>43.0</td>\n", "      <td>54.9</td>\n", "      <td>52.0</td>\n", "      <td>55.9</td>\n", "      <td>46.0</td>\n", "      <td>55.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>55.6</td>\n", "      <td>62.0</td>\n", "      <td>56.3</td>\n", "      <td>39.0</td>\n", "      <td>55.2</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane                 Ben          \n", "product      Product A Product B Product A Product B Product A Product B\n", "year quarter                                                            \n", "2019 2            49.0      56.3      54.0      55.4      48.0      54.5\n", "     1            51.0      55.7      58.0      53.5      67.0      52.9\n", "2018 2            43.0      54.9      52.0      55.9      46.0      55.0\n", "     1            39.0      55.6      62.0      56.3      39.0      55.2"]}, "execution_count": 72, "metadata": {}, "output_type": "execute_result"}], "source": ["# hierarchical indices and columns\n", "index = pd.MultiIndex.from_product([[2019, 2018], [2, 1]],\n", "                                   names=['year', 'quarter'])\n", "columns = pd.MultiIndex.from_product([['John', 'Jane', 'Ben'], ['Product A', 'Product B']],\n", "                                     names=['sales person', 'product'])\n", "\n", "# mock some data\n", "data = np.round(np.random.randn(4, 6), 1)\n", "data[:, ::2] *= 10\n", "data += 55\n", "\n", "# create the DataFrame\n", "sales_data = pd.DataFrame(data, index=index, columns=columns)\n", "sales_data"]}, {"cell_type": "markdown", "metadata": {}, "source": ["`sales_data` is essentially four dimensional data with 'sales person', 'product', 'year' and 'quarter' as its dimensions."]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Subset Selection"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Since columns in a `DataFrame` are individual `Series`, the syntax used for multi-indexed `Series` applies to the columns."]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Select data for 'Product A' sold by John"]}, {"cell_type": "code", "execution_count": 73, "metadata": {}, "outputs": [{"data": {"text/plain": ["year  quarter\n", "2019  2          49.0\n", "      1          51.0\n", "2018  2          43.0\n", "      1          39.0\n", "Name: (John, Product A), dtype: float64"]}, "execution_count": 73, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data['John','Product A']"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Select data for John and Jane"]}, {"cell_type": "code", "execution_count": 74, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2019</th>\n", "      <th>2</th>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2018</th>\n", "      <th>2</th>\n", "      <td>43.0</td>\n", "      <td>54.9</td>\n", "      <td>52.0</td>\n", "      <td>55.9</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>55.6</td>\n", "      <td>62.0</td>\n", "      <td>56.3</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane          \n", "product      Product A Product B Product A Product B\n", "year quarter                                        \n", "2019 2            49.0      56.3      54.0      55.4\n", "     1            51.0      55.7      58.0      53.5\n", "2018 2            43.0      54.9      52.0      55.9\n", "     1            39.0      55.6      62.0      56.3"]}, "execution_count": 74, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data[['John','Jane']]"]}, {"cell_type": "markdown", "metadata": {}, "source": ["__`.loc`__ and __`.iloc`__ index operators can also be used."]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Sales data for first two rows and first four columns"]}, {"cell_type": "code", "execution_count": 75, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2019</th>\n", "      <th>2</th>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane          \n", "product      Product A Product B Product A Product B\n", "year quarter                                        \n", "2019 2            49.0      56.3      54.0      55.4\n", "     1            51.0      55.7      58.0      53.5"]}, "execution_count": 75, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.iloc[:2, :4]"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Jane's sales in 2019"]}, {"cell_type": "code", "execution_count": 76, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["product  Product A  Product B\n", "quarter                      \n", "2             54.0       55.4\n", "1             58.0       53.5"]}, "execution_count": 76, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.loc[2019,'Jane']"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Jane's sales in 2019 for Product B"]}, {"cell_type": "code", "execution_count": 77, "metadata": {}, "outputs": [{"data": {"text/plain": ["quarter\n", "2    55.4\n", "1    53.5\n", "Name: (Jane, Product B), dtype: float64"]}, "execution_count": 77, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.loc[2019,('Jane','Product B')]"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Jane's sales in second quarter of 2019 for Product B"]}, {"cell_type": "code", "execution_count": 78, "metadata": {}, "outputs": [{"data": {"text/plain": ["55.4"]}, "execution_count": 78, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.loc[(2019,2), ('Jane','Product B')]"]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Sorting"]}, {"cell_type": "markdown", "metadata": {}, "source": ["##### By Index and Level\n", "\n", "`sort_index()` can be used to sort the index or levels within your data. By default, the indexing operartion is performed on the outermost index (level=0) and in ascending order."]}, {"cell_type": "code", "execution_count": 79, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2018</th>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>55.6</td>\n", "      <td>62.0</td>\n", "      <td>56.3</td>\n", "      <td>39.0</td>\n", "      <td>55.2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>43.0</td>\n", "      <td>54.9</td>\n", "      <td>52.0</td>\n", "      <td>55.9</td>\n", "      <td>46.0</td>\n", "      <td>55.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2019</th>\n", "      <th>1</th>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "      <td>67.0</td>\n", "      <td>52.9</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "      <td>48.0</td>\n", "      <td>54.5</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane                 Ben          \n", "product      Product A Product B Product A Product B Product A Product B\n", "year quarter                                                            \n", "2018 1            39.0      55.6      62.0      56.3      39.0      55.2\n", "     2            43.0      54.9      52.0      55.9      46.0      55.0\n", "2019 1            51.0      55.7      58.0      53.5      67.0      52.9\n", "     2            49.0      56.3      54.0      55.4      48.0      54.5"]}, "execution_count": 79, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sort_index()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Specifying `level=1` sorts by the inner index."]}, {"cell_type": "code", "execution_count": 80, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>2018</th>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>55.6</td>\n", "      <td>62.0</td>\n", "      <td>56.3</td>\n", "      <td>39.0</td>\n", "      <td>55.2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2019</th>\n", "      <th>1</th>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "      <td>67.0</td>\n", "      <td>52.9</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2018</th>\n", "      <th>2</th>\n", "      <td>43.0</td>\n", "      <td>54.9</td>\n", "      <td>52.0</td>\n", "      <td>55.9</td>\n", "      <td>46.0</td>\n", "      <td>55.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2019</th>\n", "      <th>2</th>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "      <td>48.0</td>\n", "      <td>54.5</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane                 Ben          \n", "product      Product A Product B Product A Product B Product A Product B\n", "year quarter                                                            \n", "2018 1            39.0      55.6      62.0      56.3      39.0      55.2\n", "2019 1            51.0      55.7      58.0      53.5      67.0      52.9\n", "2018 2            43.0      54.9      52.0      55.9      46.0      55.0\n", "2019 2            49.0      56.3      54.0      55.4      48.0      54.5"]}, "execution_count": 80, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sort_index(level=1)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Sorting can be applied on columns by specifying `axis='columns'`."]}, {"cell_type": "code", "execution_count": 81, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2019</th>\n", "      <th>2</th>\n", "      <td>48.0</td>\n", "      <td>54.5</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>67.0</td>\n", "      <td>52.9</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2018</th>\n", "      <th>2</th>\n", "      <td>46.0</td>\n", "      <td>55.0</td>\n", "      <td>52.0</td>\n", "      <td>55.9</td>\n", "      <td>43.0</td>\n", "      <td>54.9</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>55.2</td>\n", "      <td>62.0</td>\n", "      <td>56.3</td>\n", "      <td>39.0</td>\n", "      <td>55.6</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person       Ben                Jane                John          \n", "product      Product A Product B Product A Product B Product A Product B\n", "year quarter                                                            \n", "2019 2            48.0      54.5      54.0      55.4      49.0      56.3\n", "     1            67.0      52.9      58.0      53.5      51.0      55.7\n", "2018 2            46.0      55.0      52.0      55.9      43.0      54.9\n", "     1            39.0      55.2      62.0      56.3      39.0      55.6"]}, "execution_count": 81, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sort_index(axis='columns')"]}, {"cell_type": "code", "execution_count": 82, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th>Ben</th>\n", "      <th>Jane</th>\n", "      <th>John</th>\n", "      <th>Ben</th>\n", "      <th>Jane</th>\n", "      <th>John</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product A</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product B</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2019</th>\n", "      <th>2</th>\n", "      <td>48.0</td>\n", "      <td>54.0</td>\n", "      <td>49.0</td>\n", "      <td>54.5</td>\n", "      <td>55.4</td>\n", "      <td>56.3</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>67.0</td>\n", "      <td>58.0</td>\n", "      <td>51.0</td>\n", "      <td>52.9</td>\n", "      <td>53.5</td>\n", "      <td>55.7</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2018</th>\n", "      <th>2</th>\n", "      <td>46.0</td>\n", "      <td>52.0</td>\n", "      <td>43.0</td>\n", "      <td>55.0</td>\n", "      <td>55.9</td>\n", "      <td>54.9</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>62.0</td>\n", "      <td>39.0</td>\n", "      <td>55.2</td>\n", "      <td>56.3</td>\n", "      <td>55.6</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person       Ben      Jane      John       Ben      Jane      John\n", "product      Product A Product A Product A Product B Product B Product B\n", "year quarter                                                            \n", "2019 2            48.0      54.0      49.0      54.5      55.4      56.3\n", "     1            67.0      58.0      51.0      52.9      53.5      55.7\n", "2018 2            46.0      52.0      43.0      55.0      55.9      54.9\n", "     1            39.0      62.0      39.0      55.2      56.3      55.6"]}, "execution_count": 82, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sort_index(axis='columns', level=1)"]}, {"cell_type": "markdown", "metadata": {}, "source": ["##### By Value\n", "`sort_values()` can be used to sort the values in a DataFrame by one or more columns."]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Sort by values of quarter and then year"]}, {"cell_type": "code", "execution_count": 83, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>2018</th>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>55.6</td>\n", "      <td>62.0</td>\n", "      <td>56.3</td>\n", "      <td>39.0</td>\n", "      <td>55.2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2019</th>\n", "      <th>1</th>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "      <td>67.0</td>\n", "      <td>52.9</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2018</th>\n", "      <th>2</th>\n", "      <td>43.0</td>\n", "      <td>54.9</td>\n", "      <td>52.0</td>\n", "      <td>55.9</td>\n", "      <td>46.0</td>\n", "      <td>55.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2019</th>\n", "      <th>2</th>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "      <td>48.0</td>\n", "      <td>54.5</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane                 Ben          \n", "product      Product A Product B Product A Product B Product A Product B\n", "year quarter                                                            \n", "2018 1            39.0      55.6      62.0      56.3      39.0      55.2\n", "2019 1            51.0      55.7      58.0      53.5      67.0      52.9\n", "2018 2            43.0      54.9      52.0      55.9      46.0      55.0\n", "2019 2            49.0      56.3      54.0      55.4      48.0      54.5"]}, "execution_count": 83, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sort_values(by=['quarter','year'])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["For multi-indexed data, column label must be unique. So, the values passed to `by=` parameter must be a tuple with elements corresponding to each level. "]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Sort by values of Product A for Ben"]}, {"cell_type": "code", "execution_count": 84, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2018</th>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>55.6</td>\n", "      <td>62.0</td>\n", "      <td>56.3</td>\n", "      <td>39.0</td>\n", "      <td>55.2</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>43.0</td>\n", "      <td>54.9</td>\n", "      <td>52.0</td>\n", "      <td>55.9</td>\n", "      <td>46.0</td>\n", "      <td>55.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2019</th>\n", "      <th>2</th>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "      <td>48.0</td>\n", "      <td>54.5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "      <td>67.0</td>\n", "      <td>52.9</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane                 Ben          \n", "product      Product A Product B Product A Product B Product A Product B\n", "year quarter                                                            \n", "2018 1            39.0      55.6      62.0      56.3      39.0      55.2\n", "     2            43.0      54.9      52.0      55.9      46.0      55.0\n", "2019 2            49.0      56.3      54.0      55.4      48.0      54.5\n", "     1            51.0      55.7      58.0      53.5      67.0      52.9"]}, "execution_count": 84, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sort_values(by=('Ben','Product A'))"]}, {"cell_type": "markdown", "metadata": {}, "source": ["Multiple columns, or a combination or column and index, can be specified by passing them as a list of tuples."]}, {"cell_type": "code", "execution_count": 85, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>2018</th>\n", "      <th>2</th>\n", "      <td>43.0</td>\n", "      <td>54.9</td>\n", "      <td>52.0</td>\n", "      <td>55.9</td>\n", "      <td>46.0</td>\n", "      <td>55.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2019</th>\n", "      <th>2</th>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "      <td>48.0</td>\n", "      <td>54.5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "      <td>67.0</td>\n", "      <td>52.9</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2018</th>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>55.6</td>\n", "      <td>62.0</td>\n", "      <td>56.3</td>\n", "      <td>39.0</td>\n", "      <td>55.2</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane                 Ben          \n", "product      Product A Product B Product A Product B Product A Product B\n", "year quarter                                                            \n", "2018 2            43.0      54.9      52.0      55.9      46.0      55.0\n", "2019 2            49.0      56.3      54.0      55.4      48.0      54.5\n", "     1            51.0      55.7      58.0      53.5      67.0      52.9\n", "2018 1            39.0      55.6      62.0      56.3      39.0      55.2"]}, "execution_count": 85, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sort_values(by=[('Jane','Product A'), ('quarter')])"]}, {"cell_type": "markdown", "metadata": {}, "source": ["#### Data Aggregations\n", "\n", "We have seen data aggregations in a previous section in this notebook. Various aggregation functions such as `sum()`, `mean()`, `median()` can be applied to multi-indexed data."]}, {"cell_type": "code", "execution_count": 86, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th></th>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "    </tr>\n", "    <tr>\n", "      <th></th>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2019</th>\n", "      <th>2</th>\n", "      <td>49.0</td>\n", "      <td>56.3</td>\n", "      <td>54.0</td>\n", "      <td>55.4</td>\n", "      <td>48.0</td>\n", "      <td>54.5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>51.0</td>\n", "      <td>55.7</td>\n", "      <td>58.0</td>\n", "      <td>53.5</td>\n", "      <td>67.0</td>\n", "      <td>52.9</td>\n", "    </tr>\n", "    <tr>\n", "      <th rowspan=\"2\" valign=\"top\">2018</th>\n", "      <th>2</th>\n", "      <td>43.0</td>\n", "      <td>54.9</td>\n", "      <td>52.0</td>\n", "      <td>55.9</td>\n", "      <td>46.0</td>\n", "      <td>55.0</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>39.0</td>\n", "      <td>55.6</td>\n", "      <td>62.0</td>\n", "      <td>56.3</td>\n", "      <td>39.0</td>\n", "      <td>55.2</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane                 Ben          \n", "product      Product A Product B Product A Product B Product A Product B\n", "year quarter                                                            \n", "2019 2            49.0      56.3      54.0      55.4      48.0      54.5\n", "     1            51.0      55.7      58.0      53.5      67.0      52.9\n", "2018 2            43.0      54.9      52.0      55.9      46.0      55.0\n", "     1            39.0      55.6      62.0      56.3      39.0      55.2"]}, "execution_count": 86, "metadata": {}, "output_type": "execute_result"}], "source": ["# Get data\n", "sales_data"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Total sales by sales person and product"]}, {"cell_type": "code", "execution_count": 87, "metadata": {}, "outputs": [{"data": {"text/plain": ["sales person  product  \n", "John          Product A    182.0\n", "              Product B    222.5\n", "Jane          Product A    226.0\n", "              Product B    221.1\n", "Ben           Product A    200.0\n", "              Product B    217.6\n", "dtype: float64"]}, "execution_count": 87, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sum()"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Total sales by year and quarter"]}, {"cell_type": "code", "execution_count": 88, "metadata": {}, "outputs": [{"data": {"text/plain": ["year  quarter\n", "2019  2          317.2\n", "      1          338.1\n", "2018  2          306.8\n", "      1          307.1\n", "dtype: float64"]}, "execution_count": 88, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sum(axis='columns')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["__`level`__ parameter controls the subset of data to which aggregation is applied. Let's look at some examples."]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Total sales for each quarter by product and sales person"]}, {"cell_type": "code", "execution_count": 89, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "    </tr>\n", "    <tr>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>quarter</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>2</th>\n", "      <td>92.0</td>\n", "      <td>111.2</td>\n", "      <td>106.0</td>\n", "      <td>111.3</td>\n", "      <td>94.0</td>\n", "      <td>109.5</td>\n", "    </tr>\n", "    <tr>\n", "      <th>1</th>\n", "      <td>90.0</td>\n", "      <td>111.3</td>\n", "      <td>120.0</td>\n", "      <td>109.8</td>\n", "      <td>106.0</td>\n", "      <td>108.1</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane                 Ben          \n", "product      Product A Product B Product A Product B Product A Product B\n", "quarter                                                                 \n", "2                 92.0     111.2     106.0     111.3      94.0     109.5\n", "1                 90.0     111.3     120.0     109.8     106.0     108.1"]}, "execution_count": 89, "metadata": {}, "output_type": "execute_result"}], "source": ["sales_data.sum(level='quarter')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Average sales for each year by product and sales person"]}, {"cell_type": "code", "execution_count": 90, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead tr th {\n", "        text-align: left;\n", "    }\n", "\n", "    .dataframe thead tr:last-of-type th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr>\n", "      <th>sales person</th>\n", "      <th colspan=\"2\" halign=\"left\">John</th>\n", "      <th colspan=\"2\" halign=\"left\">Jane</th>\n", "      <th colspan=\"2\" halign=\"left\">Ben</th>\n", "    </tr>\n", "    <tr>\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>2019</th>\n", "      <td>50.0</td>\n", "      <td>56.00</td>\n", "      <td>56.0</td>\n", "      <td>54.45</td>\n", "      <td>57.5</td>\n", "      <td>53.7</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2018</th>\n", "      <td>41.0</td>\n", "      <td>55.25</td>\n", "      <td>57.0</td>\n", "      <td>56.10</td>\n", "      <td>42.5</td>\n", "      <td>55.1</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["sales person      John                Jane                 Ben          \n", "product      Product A Product B Product A Product B Product A Product B\n", "year                                                                    \n", "2019              50.0     56.00      56.0     54.45      57.5      53.7\n", "2018              41.0     55.25      57.0     56.10      42.5      55.1"]}, "execution_count": 90, "metadata": {}, "output_type": "execute_result"}], "source": ["yearly_avg = sales_data.mean(level='year')\n", "yearly_avg"]}, {"cell_type": "markdown", "metadata": {}, "source": ["- Average sales per year by Product"]}, {"cell_type": "code", "execution_count": 91, "metadata": {}, "outputs": [{"data": {"text/html": ["<div>\n", "<style scoped>\n", "    .dataframe tbody tr th:only-of-type {\n", "        vertical-align: middle;\n", "    }\n", "\n", "    .dataframe tbody tr th {\n", "        vertical-align: top;\n", "    }\n", "\n", "    .dataframe thead th {\n", "        text-align: right;\n", "    }\n", "</style>\n", "<table border=\"1\" class=\"dataframe\">\n", "  <thead>\n", "    <tr style=\"text-align: right;\">\n", "      <th>product</th>\n", "      <th>Product A</th>\n", "      <th>Product B</th>\n", "    </tr>\n", "    <tr>\n", "      <th>year</th>\n", "      <th></th>\n", "      <th></th>\n", "    </tr>\n", "  </thead>\n", "  <tbody>\n", "    <tr>\n", "      <th>2019</th>\n", "      <td>54.500000</td>\n", "      <td>54.716667</td>\n", "    </tr>\n", "    <tr>\n", "      <th>2018</th>\n", "      <td>46.833333</td>\n", "      <td>55.483333</td>\n", "    </tr>\n", "  </tbody>\n", "</table>\n", "</div>"], "text/plain": ["product  Product A  Product B\n", "year                         \n", "2019     54.500000  54.716667\n", "2018     46.833333  55.483333"]}, "execution_count": 91, "metadata": {}, "output_type": "execute_result"}], "source": ["yearly_avg.mean(axis=1, level='product')"]}, {"cell_type": "markdown", "metadata": {}, "source": ["## Conclusion"]}, {"cell_type": "markdown", "metadata": {}, "source": ["In this part of the guide series we learned about how to be more productive with __[Pandas](https://pandas.pydata.org/)__. We started with Data Aggregation using `groupby` and  `pivot_table`. Next, we discussed how data can be combined using `concat()`, `append()`, `merge()`, and `join()` methods. You have seen how data can be indexed at multiple levels in the Hierarchical indexing section. Here, we discussed multi-indexed `Series` and `DataFrame`, including selection, sorting, and aggregation methods. We also looked at how to look at unique values and value counts for categorical data.\n", "\n", "In the next part of this guide series, we will explore the capabilities for working with Time Series data."]}, {"cell_type": "markdown", "metadata": {}, "source": ["## References"]}, {"cell_type": "markdown", "metadata": {}, "source": ["[1] Wes McKinney. 2017. Python for Data Analysis: Data Wrangling with Pandas, NumPy, and IPython (2nd. ed.). O'Reilly Media, Inc.    \n", "    \n", "[2] Jake VanderPlas. 2016. Python Data Science Handbook: Essential Tools for Working with Data (1st. ed.). O'Reilly Media, Inc.    "]}], "metadata": {"anaconda-cloud": {}, "kernelspec": {"display_name": "Python 3", "language": "python", "name": "python3"}, "language_info": {"codemirror_mode": {"name": "ipython", "version": 3}, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.3"}, "livereveal": {"scroll": true}, "toc": {"base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": true, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": true, "toc_position": {"height": "calc(100% - 180px)", "left": "10px", "top": "150px", "width": "202px"}, "toc_section_display": true, "toc_window_display": true}}, "nbformat": 4, "nbformat_minor": 2}